Trigger granularity?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Trigger granularity?

  1. #1
    Join Date
    Aug 2002
    Posts
    3

    Question

    I am building an audit table that only has a single data column to record changes taking place in the user tables. Most of changes will be updating a single column in user tables.
    I am using a trigger type of (for each row), but that leads me to a problem with updating multiple columns at one time on the user's tables. If the trigger only fires once for an update, is there any way to create multiple rows (one for each column in user table) inside of the trigger. I can't see how my current format of trigger could be adjusted that way. Example of current trigger below.

    CREATE OR REPLACE
    TRIGGER "EIIS_TEST"."SHIP_SETTING_TRIGGER"
    AFTER INSERT OR DELETE OR UPDATE OF "SHIP_SETTING", "ID", "RMV_ID", "EIISFRAG_ID"
    ON "EIIS_TEST"."SHIP_SETTINGS"
    FOR EACH ROW
    Declare
    v_master_id number;

    Begin

    If updating ('SHIP_SETTING') then
    -- insert into audit table

    elsif updating ('ID') then
    -- insert into audit table

    ..
    But what if both of these columns are updated? The code will just do the first insert and then end (I think). Any ways that I am not seeing. As you can probably tell I am pretty new to PL/SQL .

    thanks for any help
    Mark

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Instead of using IF-ELSIF-ENDIF logic, use IF-ENDIF, IF-ENDIF. That way, every condition will be tested, regardles if previous ones have evaluated to TRUE or not. So for your example, use:

    Begin
    If updating then

    IF nvl(:NEW.SHIP_SETTING,'blahblah') != nvl(:OLD.SHIP_SETTING,'blahblah') then
    -- insert into audit table
    ENDIF;

    IF nvl(:NEW.ID,-99999) != nvl(:OLD.ID,-99999) then
    -- insert into audit table
    ENDIF;
    ...
    ...
    ...

    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Aug 2002
    Posts
    3
    Thanks jmodic

    I figured that it would be something simple that I was just overlooking.


    Mark Burns

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width