CODEWITHSUNDEEP
oracle-databaseplsqltriggerscursor
MayankG
MayankG

Reputation: 57

Inserting multiple records from a trigger in oracle

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

can any1 help me with this code?

Regards

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;

Upvotes: 1

Views: 3135

Answers (2)

ShoeLace
ShoeLace

Reputation: 3586

to insert multiple rows you will need either a LOOP or some form of "INSERT... SELECT" statement.

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;

OR

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;

Upvotes: 0

David Aldridge
David Aldridge

Reputation: 52376

Triggers != Application code

If you embed application code in trigger like this then it will be horrible to maintain and debug and you'll always be encountering situations where a trigger-based approach won't work because of a mutating table error.

You would do much better to keep triggers for only auditing and other non-application activities, and put this kind of logic in the application itself.

Upvotes: 5

Related Questions