CODEWITHSUNDEEP
sql
jason.kaisersmith
jason.kaisersmith

Reputation: 9610

When exactly is an AFTER DELETE trigger fired

I hope that you can help me on some SQL theory, as I am not 100% sure how this works.

If I have a trigger and I define it as

AFTER DELETE ON xxxx

I was wondering when exactly this would fire, in terms of transaction management?

So if I delete a record from my table I assume that the trigger will not fire until I type commit and finish the transaction. Is this correct?

If so, then I assume that if the commit on my delete statement works but the trigger fails for some reason then only the trigger would be rolled back, and the original executed delete statement that I performed would still be committed (because I have it defined as AFTER DELETE).

Can somebody please confirm this?

Thanks.

Upvotes: 4

Views: 2325

Answers (3)

Philipp
Philipp

Reputation: 69663

The purpose of SQL triggers is to ensure referential consistency. But when they would be exectued in a separate transaction commit, there would be the possibility that they leave data in an inconsistent state.

So the delete trigger is executed the moment you do the delete command. When this happens as a transaction and you roll it back, the triggered delete is also rolled back.

Upvotes: 2

Srini V
Srini V

Reputation: 11355

1. You delete a row on TABLE1 no COMMIT;
2. TRIGGER performs an action (This takes place before COMMIT or ROLLBACK for step1, but trigger will not have any commit or rollback in it)
3a. You apply commit - Both step1 and step2 gets completed .
3b. You apply rollback- Both step1 and step2 rolled back.

Either you give 3a or 3b

Upvotes: 4

Mureinik
Mureinik

Reputation: 311188

An AFTER DELETE trigger is fired after the delete statement is executed, and before the control is returned to the user - i.e., he perceives the delete statement and the code executed after it in a trigger as a single action (assuming the trigger just does DMLs and nothing funky like calling UTL_TCP :-)).

This has nothing to do with transaction management - once the DELETE and the AFTER DELETE trigger execute, you can choose to commit, to rollback, or to continue performing DML statements in the same transaction.

Upvotes: 1

Related Questions