Update trigger on Oracle 11gR2 database

Posted on 2011-10-13
Medium Priority
Last Modified: 2012-06-21
Can I write Before update and After update trigger at the same time...

I have two tables:
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

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.

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..

Question by:toooki
  • 2
LVL 35

Accepted Solution

sarabande earned 1900 total points
ID: 36967350
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.

   ON Tab1
   REFERENCING OLD AS oldtab1 NEW AS newtab1
   DECLARE num integer;
   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;

Open in new window


Author Comment

ID: 36970115
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..?

Author Comment

ID: 36976276
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.


Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
What we learned in Webroot's webinar on multi-vector protection.
This video shows how to recover a database from a user managed backup
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

830 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