CODEWITHSUNDEEP
sqloracle-database
Валентин Д.
Валентин Д.

Reputation: 25

Fire trigger after commit on Oracle

i'm writing sql trigger that updates some columns in table. I have to write that this trigger fires only after commit. I read some articles about this topic but in the most it is said to use procedure. But I have to do it with trigger.

 CREATE OR REPLACE TRIGGER tr_reset_er_encours
 AFTER INSERT OR UPDATE ON T
 FOR EACH ROW
 BEGIN
 IF (:NEW.T.var1)=0
 THEN UPDATE T 
  SET var1=NULL
 END IF;
 END;

Upvotes: 0

Views: 127

Answers (1)

Littlefoot
Littlefoot

Reputation: 142705

Trigger fires after insert or update on the t table. You can't (at least, as far as I can tell) instruct it to fire after commit.

Apart from that, rewrite it to

CREATE OR REPLACE TRIGGER tr_reset_er_encours
   BEFORE INSERT OR UPDATE
   ON t
   FOR EACH ROW
   WHEN (new.var1 = 0)
BEGIN
   :new.var1 := NULL;
END;

as updating the same table on which trigger fires results in the mutating table error.

Saying that you read that procedure should be used: basically, you should commit at the end of the transaction. It is pointless to

  • do something (in the procedure)
  • commit
  • do something else
  • never commit again (as you'll lose what you did in "something else")

Upvotes: 1

Related Questions