Solved

can we declare variables and use in trigger body?

Posted on 2003-10-28
6
1,807 Views
Last Modified: 2012-08-14
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.

0
Comment
Question by:sajidh_unisa
6 Comments
 
LVL 12

Expert Comment

by:catchmeifuwant
ID: 9632488
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;
/
0
 
LVL 8

Accepted Solution

by:
Danielzt earned 250 total points
ID: 9633708


just like this:

CREATE OR REPLACE TRIGGER trigger1
BEFORE update or delete on temp

declare

-- put all variable definition here

v_test varchar2(1) ;

-- trigger body begins
begin

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

end;
0
 
LVL 4

Expert Comment

by:Hassan_Ghanem
ID: 9633730
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..
0
 
LVL 4

Expert Comment

by:Vinay_dba
ID: 9635112
Yes you can declare a variable say var1 and set it as 'U' or 'D' based on the DML.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

861 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now