Update trigger on Oracle 11gR2 database

Posted on 2011-10-13
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
    LVL 32

    Accepted Solution

    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

    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

    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.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Read about achieving the basic levels of HRIS security in the workplace.
    From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

    761 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

    14 Experts available now in Live!

    Get 1:1 Help Now