When exactly is an AFTER DELETE trigger fired

Question

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.

Answer 1

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