7 Replies Latest reply: Dec 3, 2012 3:53 AM by Sachinmrt RSS

    How to know Trigger event name

    Sachinmrt
      Dear all,

      I have one question please tell me how to know the trigger event name in trigger calling...
      like-
      CREATE OR REPLACE TRIGGER ******
      BEFORE DELETE OR INSERT OR UPDATE
      OF TOTAL_QUANTITY,CANCELLED
      ON *********
      REFERENCING NEW AS NEW OLD AS OLD
      FOR EACH ROW
      BEGIN
      -----------I want to know given trigger event at this level-----------
        IF INSERTING  THEN
          null;
        ELSIF UPDATING  THEN
         null;
        ELSIF DELETING THEN
           null;
        END IF;

      END;
      Please help...
        • 1. Re: How to know Trigger event name
          jeneesh
          I am confused..
          IF INSERTING  THEN
               ..--"INSERT is the trigger event, what is your question?
            ELSIF UPDATING  THEN
             null;
            ELSIF DELETING THEN
               null;
            END IF;
          
          A sample...(Not to use, just demo
          create table test(c1 number);
          
          
          create or replace trigger trig1 
            before update or delete or insert on test
            for each row
          begin
           if inserting then
            dbms_output.put_line('Insert Event');
           elsif updating then
            dbms_output.put_line('Update Event');
           else 
            dbms_output.put_line('Delete Event');
           end if;
          end;
          
          TRIGGER TRIG1 compiled
          
          insert into test values(1);
          
          1 rows inserted.
          
          Insert Event
          
          {code}
          
          Edited by: jeneesh on Dec 3, 2012 2:59 PM                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
          • 2. Re: How to know Trigger event name
            Sachinmrt
            sir my question is can i store the trigger event name before IF INSERTING  THEN
            create or replace trigger trig1 
              before update or delete or insert on test
              for each row
            begin
            -----var_tri_name:=inserting/updating/deletion-----------
            if inserting then
              dbms_output.put_line('Insert Event');
            elsif updating then
              dbms_output.put_line('Update Event');
            else
              dbms_output.put_line('Delete Event');
            end if;
            end;
            i thing it's not possible to track the oracle utility..

            Edited by: Sachingmrt on Dec 3, 2012 3:03 PM
            • 3. Re: How to know Trigger event name
              jeneesh
              You can have a block there, right?

              What issue are you truing to solve?
              create or replace trigger trig1 
                before update or delete or insert on test
                for each row
              declare  
                lc_event varchar2(50);
              begin
               if inserting then
                lc_event := 'INSERT';
               elsif updating then
                lc_event := 'UPDATE';
               else 
                lc_event := 'DELETE';
               end if;
               if inserting then --"or use your variable.."
                 ......  
              • 4. Re: How to know Trigger event name
                Sachinmrt
                Sir,
                My issue is i want to wrap my all procedure,function and trigger. Procedure,function are wrap easily but trigger not.
                So want to wrap the trigger with help of procedure. I easily create the procedure and called into the trigger.
                Now i need to know the name of trigger-event for create the procedure.
                • 5. Re: How to know Trigger event name
                  jeneesh
                  I dont think event atribute functions are avilable for DML

                  http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/triggers.htm#i1007895

                  You could use a procedure like
                  proc(p_event_type varchar2);
                  And then call it in trigger
                  if inserting then
                   proc('INSERT);
                  ...
                  • 6. Re: How to know Trigger event name
                    myOra_help
                    why don't you call your procedure like
                    ...
                     if inserting then
                       proc1;
                     elsif updating then
                      proc2;
                     else
                     proc3;
                     end if;
                    ...
                    after all your actual code is wrapped in procedure.
                    • 7. Re: How to know Trigger event name
                      Sachinmrt
                      Because my trigger code is so complex..