CODEWITHSUNDEEP
oracle-databaseoracle11gdatabase-trigger
ZerOne
ZerOne

Reputation: 1326

Oracle trigger before and after insert

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?

Upvotes: 0

Views: 680

Answers (1)

XING
XING

Reputation: 9886

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;

Upvotes: 4

Related Questions