Forum Stats

  • 3,855,055 Users
  • 2,264,455 Discussions
  • 7,905,882 Comments

Discussions

PL/SQL TRIGGER BEFORE DELETE OR INSERT OR UPDATE

800039
800039 Member Posts: 5
edited Sep 24, 2010 10:47AM in SQL & PL/SQL
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
Tagged:

Best Answer

  • Hoek
    Hoek Member Posts: 16,087 Gold Crown
    edited Sep 24, 2010 10:12AM Answer ✓
    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

Answers

  • Hoek
    Hoek Member Posts: 16,087 Gold Crown
    edited Sep 24, 2010 10:12AM Answer ✓
    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
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,729 Red Diamond
    >
    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.
    Solomon Yakobson
  • 789895
    789895 Member Posts: 753
    edited Sep 24, 2010 10:16AM
    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
    789895
  • 800039
    800039 Member Posts: 5
    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
  • Hoek
    Hoek Member Posts: 16,087 Gold Crown
    edited Sep 24, 2010 10:27AM
    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.
    Hoek
  • 789895
    789895 Member Posts: 753
    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
    789895
  • 800039
    800039 Member Posts: 5
    Dear


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


    Have good weekend and till later!



    Christof
This discussion has been closed.