7 Replies Latest reply: Sep 24, 2010 9:47 AM by 800039 RSS

    PL/SQL TRIGGER BEFORE DELETE OR INSERT OR UPDATE

    800039
      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
          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
            >
            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
              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
                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
                  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
                    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
                      Dear


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


                      Have good weekend and till later!



                      Christof