Oracle trigger before and after insert

Question

Is it possible to create only one trigger in Oracle that fires before and after data got inserted into a table?

begin

if inserting THEN
   -- This code should be run before insertion:
   :new.cr_timestamp := SYSTIMESTAMP;

   IF :NEW.id IS NULL THEN
      SELECT sq_table_id.NEXTVAL
      INTO :NEW.id
      FROM dual;
   END IF;

-- Now I want code which run after the insertion..

end if;

end;

So is there a flag which indicates if its before or after the insertion?

[compound trigger](http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/triggers.htm#CIHEFGFD). — Arkadiusz Łukasiewicz, Jul 12 '16 at 13:07

score 4 · Accepted Answer · answered Jul 12 '16 at 13:24

It can be written as below.

CREATE OR REPLACE TRIGGER compound_trigger_name
FOR [INSERT|DELETE|UPDATE [OF column] ON table
COMPOUND TRIGGER

--Executed before DML statement
     BEFORE STATEMENT IS
     BEGIN
       NULL;
     END BEFORE STATEMENT;

   --Executed aftereach row change- :NEW, :OLD are available
     AFTER EACH ROW IS
     BEGIN
       NULL;
     END AFTER EACH ROW;

     --Executed after DML statement
     AFTER STATEMENT IS
     BEGIN
       NULL;
     END AFTER STATEMENT;

END compound_trigger_name;

Oracle trigger before and after insert

1 Answers1