asked on

can we declare variables and use in trigger body?

Hi Experts,

Can someone tell me if we can declare variables like how we do in normal PL/SQL block and use it in trigger? My problem is i need to write an Before Update/Delete trigger on Table 1. In the trigger body, I need to insert data in to another table Table 2. Table 2 has a field called Action code, If its an Update then the action code should be set as 'U' , if its a delete then action code should be set as 'D'.

I know i need to use the 'INSERTING' or 'UPDATING' clause to find out the DML operation. I just wanted to know if I can declare a variable say var1 and set it as 'U' or 'D' based on the DML.

If I cant do the above, then I need to repeat the entire Insert statement with just the action code differing. Pls help.

catchmeifuwant

Yes you can declare a variable and use it...heres an example..

CREATE OR REPLACE TRIGGER trg_test
BEFORE update or delete on emp
BEGIN
declare
v_test varchar2(1) ;
begin

if UPDATING then
v_test := 'U';
else
v_test := 'D';
end if;
insert into temp values(v_test);
end;
END;
/

ASKER CERTIFIED SOLUTION

Danielzt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Hassan_Ghanem

of course you can, because the only difference between triggers and procedures is that triggers fire automatically before/after a DML operation but procedure should be called explicitly..

Vinay_dba

Yes you can declare a variable say var1 and set it as 'U' or 'D' based on the DML.