Update trigger on Oracle 11gR2 database

Can I write Before update and After update trigger at the same time...

I have two tables:
Tab1
---------------
F1            F2           F3               F4           F5             F6
abc       xx             10/1/2011      PP1       20            John
pqr        yy            2/2/2011         PP2       30            Joe
mno      zz            7/7/2011         PP3       66            Jef

Tab2
-------------------------------      
F6             F5_before_after
John2        20, 22
Joe2          30, 85

How can I write a before and/or after UPDATE trigger that will enter a new record in the log table Tab2 only when these conditions are met:
The value of F5 was updated for a given F1, F2, F3 and F4 combination in Tab1. And the Tab2 will then enter the before and after value of F5 comma separated in the second field in Tab2. Also that record will have the first field value = new value for F6.

For example:
Update Tab1 set  F5 = 22 and F6=John2
where F1 = 'abc' and F2= 'xx' and F3=to_date('10/1/2011', 'mm/dd/yyyy') and F4 = 'PP1';
It will enter a record as in the first record in Tab2.

Similarly:
Update Tab1 set  F5 = 20 and F6=John2
where F1 = 'abc' and F2= 'xx' and F3=to_date('10/1/2011', 'mm/dd/yyyy') and F4 = 'PP1';
It will not enter a new record.

Could you please help me write one trigget that way? I cannot combine a before and after update trigger..




toookiAsked:
Who is Participating?
 
sarabandeConnect With a Mentor Commented:
i think what you will do is a before update trigger if i rightly understand your requirements.

if you never want to change any of the new update values you also may do it as after update trigger.

CREATE OR REPLACE TRIGGER Upd_Tab1
   BEFORE UPDATE
   ON Tab1
   REFERENCING OLD AS oldtab1 NEW AS newtab1
   FOR EACH ROW
   DECLARE num integer;
BEGIN
   IF :newtab1.f5 != :oldtab1.f5 THEN
        SELECT COUNT(*) INTO num 
        FROM TAB2 
        WHERE f6 = :newtab1.f6 AND f5_before_after = :oldtab1.f5||Char(',')||:newtab1.f5; 
        IF num = 0 THEN
           INSERT INTO TAB2 (f6, f5)
           VALUES (:newtab1.f6, :oldtab1.f5||','||:newtab1.f5);    
        END IF
   END IF;
END;

Open in new window


Sara
0
 
toookiAuthor Commented:
Many thanks. It mostly works for me.

But it is inserting multiple records in the log.
Is it possible to store the value:

:newtab1.f6, :oldtab1.f5||','||:newtab1.f5

in a variable or so? And in the next time (next FOR EACH ROW) we do not insert the same string if the string we are going to insert was inserted before by the trigger in the past run..?
0
 
toookiAuthor Commented:
Thank you again.

I wrote an INSERT into log table statement following the UPDATE statement in the main code itself (without trigger). Which works as expected.

I used your trigger code for each row updated. And that worked perfectly.

Thanks a lot.

 
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.