Question

i created a trigger to insert the employeeIDs whenever a position is updated. In some cases, there are many employees attached to one position so the trigger isnt able to insert all the employees (only 1). i need all the employee Ids

有谁能帮我解码吗?

观观观

create or replace trigger postn_updt
after update on postn
for each row

declare 

cursor m is 
select u.login
from user u,
party_per s
where u.row_id=s.person_id 
and s.party_id=:new.row_id;

rownum varchar2(10);

begin
if updating ( postn_type_cd ) then
open mult;
fetch mult into rownum;

insert into test123
(employee_number,type,request_date,remarks)
values
(( rownum,
 Updated ,sysdate,  
);
close m;
end if;
end;

Answer 1

触发器! @ label

如果你将应用程序代码嵌入像这样的触发器,那么维持和调试将是可怕的,而且你总是会遇到这样的情况:基于触发的方法由于变异表格错误而不能奏效。

您最好只为审计和其他不应用活动保留触发器,并将这种逻辑纳入应用本身。

Answer 2

,插入多个行,您需要 LOOP 或某种形式的“INSTERT... sELECT” 语句。

eg. 缩略语

create or replace trigger postn_updt
after update on postn
for each row

declare 

cursor m is 
select u.login
from user u,
party_per s
where u.row_id=s.person_id 
and s.party_id=:new.row_id;

begin
if updating ( postn_type_cd ) then

  for mult_rec in  m LOOP

    insert into test123
    (employee_number,type,request_date,remarks)
    values
    (( mult_rec.login,
     Updated ,sysdate,  
    );
  END LOOP;

end if;
end;

或

create or replace trigger postn_updt
after update on postn
for each row

declare 
begin
if updating ( postn_type_cd ) then

    insert into test123
    (employee_number,type,request_date,remarks)
    select u.login , Updated ,sysdate,  
    from user u,
        party_per s
    where u.row_id=s.person_id 
    and s.party_id=:new.row_id;

end if;
end;

友情链接