This discussion is archived
7 Replies Latest reply: Sep 24, 2010 7:47 AM by 800039 RSS

PL/SQL TRIGGER BEFORE DELETE OR INSERT OR UPDATE

800039 Newbie
Currently Being Moderated
Dear


I have 2 tables; Catalog and Catalog_bak (read back as backup)

I need a trigger which, after a DML (INSERT/UPDATE/DELET), copies all the info from the Catalog table to Catalog_bak table adding 2 extra fields: change_type, change_date.

The field change_type has to show a value responding to the DML which fired the trigger. If the trigger is fired through a
INSERT ==> 1
UPDATE ==> 2
DELETE ==> 3

So my question is: How can I determine if it was an INSERT, UPDATE or DELETE command which fired this trigger.


Yours Sincerely



Christof
  • 1. Re: PL/SQL TRIGGER BEFORE DELETE OR INSERT OR UPDATE
    Hoek Guru
    Currently Being Moderated
    Welcome to the forum.

    You can refer to the Online Oracle Documentation, to learn more about triggers and the options you have:
    http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_triggers.htm#sthref1249

    Doc. homes:
    http://www.oracle.com/pls/db102/homepage
    http://www.oracle.com/pls/db112/homepage

    So, in your case it would be something like:
    ...
    begin
      if inserting 
      then 
         do the required stuff..
      elsif updating 
      then 
         do the required stuff...
      elsif deleting 
      then 
         do the required stuff...
      end if;
    end;            
    More examples:
    http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_triggers.htm#ABC1032282
  • 2. Re: PL/SQL TRIGGER BEFORE DELETE OR INSERT OR UPDATE
    Solomon Yakobson Guru
    Currently Being Moderated
    >
    So my question is: How can I determine if it was an INSERT, UPDATE or DELETE command which fired this trigger.
    So you have one BEFORE/AFTER INSERT OR UPDATE OD DELETE trigger, right? Then in triiger body yoy can reference INSERTING, UPDATEING, DELETEING. For example:
    .
    .
    .
    IF INSERTING
      THEN ... 
    ELSIF UPDATING
      THEN ...
      ELSE ... -- DELETING logic
    END IF;
    .
    .
    .
    SY.
  • 3. Re: PL/SQL TRIGGER BEFORE DELETE OR INSERT OR UPDATE
    789895 Expert
    Currently Being Moderated
    Hi,

    This statement might help you in understanding how to determine the operation
    If more than one type of DML operation can fire a trigger (for example, ON INSERT OR DELETE OR UPDATE OF Emp_tab), the trigger body can use the conditional predicates INSERTING, DELETING, and UPDATING to check which type of statement fire the trigger.
    cheers

    VT
  • 4. Re: PL/SQL TRIGGER BEFORE DELETE OR INSERT OR UPDATE
    800039 Newbie
    Currently Being Moderated
    Dear


    Thank you for your fast answer. I love it.
    the keywords inserting, updating and deleting, which you use in the if clauses, are Oracle functions or? how do I know what happened?


    Yours Sincerely


    Christof
  • 5. Re: PL/SQL TRIGGER BEFORE DELETE OR INSERT OR UPDATE
    Hoek Guru
    Currently Being Moderated
    the keywords inserting, updating and deleting, which you use in the if clauses, are Oracle functions or?
    Yes, they are already there for you, if that's what you mean.
    They're built in and come shipped with the database.
    You can just refer to them as showed in the examples.
    They're not 'functions' but 'conditional predicates'.
    how do I know what happened?
    Oracle knows, and it's up to you how to handle the different action that should be taken, hence the IF ...THEN... construction.
    You can test what happened by using DBMS_OUTPUT.PUT_LINE('some message'); in each part of your conditional processing.
    Spend a little time exploring the provided links,to learn more, it's all explained there in detail, with examples.
  • 6. Re: PL/SQL TRIGGER BEFORE DELETE OR INSERT OR UPDATE
    789895 Expert
    Currently Being Moderated
    Hi,

    INSERTING corresponds to INSERT
    UPDATING corresponds to UPDATE
    DELETING corresponds to DELETE

    Oracle will fire the INSERTING event when an insert happens and the trigger has to just handle the INSERTING event to do the necessary job. Similarly it holds true for others.

    cheers

    VT
  • 7. Re: PL/SQL TRIGGER BEFORE DELETE OR INSERT OR UPDATE
    800039 Newbie
    Currently Being Moderated
    Dear


    Thank you very much,
    this was indeed the answer to my question.


    Have good weekend and till later!



    Christof

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points