I am creating a TRIGGER that need to be fired ONLY ONCE.
And if it is fired, just once it is becuase of a COMMIT (I think they would fire-then-undo if the transaction ROLLEDBACK). I want to make sure that they fire on a COMMIT and not on a ROLLBACK. (Am I making sense?)
Here was my original design:
CREATE OR REPLACE TRIGGER TR_TABLE_A
AFTER INSERT OR UPDATE OR DELETE OF COL1 ON TABLE_A
FOR EACH ROW
INSERT INTO TABLE_B (COL1) VALUES ( 'TABLE_A WAS MODIFIED');
My 2 questions:
1. How can I execute a DDL (alter trigger TR_TABLE_A disable) within a PL/SQL block, as a way to disable the trigger after it has fired once.
2. How can I ensure that the the trigger fires when the transaction was COMMITTED and not ROLLEDBACK (otherwise, after the first firing, the trigger will be disabled but there was actually no change on TABLE_A).