Prevent record from being inserted in the table in After Trigger

Question

I have a scenario where I have to insert a record in TableA only if the RecordId exists in TableB.

I have written an Instead of Trigger on TableA for Insert, in which I am checking if the RecordId exists in TableB, and if it exists then I am allowing the Insert in TableA by explicitly inserting the record in TableA in that trigger.

I want to know can I write an After Trigger on TableA, in which I do the same check, and in case if RecordId is not found in TableB then I delete the inserted record from TableA.

My question is If I fire delete query on TableA for the same record which I am inserting will it work, and prevent the record from being inserted from the TableA actually.

Answer 1

I do not see this as the most desirable implementation. Using a Foreign Key constraint to enforce correctness is (in my opinion) much better than writing code to handle such issues.

However, regarding your question, Yes, TableA can have both an INSTEAD OF trigger and an AFTER trigger. (In fact it can have several AFTER triggers.)

https://msdn.microsoft.com/en-us/library/ms189799.aspx outlines some rules, including:

1. If constraints exist on the trigger table, they are checked after the INSTEAD OF trigger execution and before the AFTER trigger execution. If the constraints are violated, the INSTEAD OF trigger actions are rolled back and the AFTER trigger is not fired.
2. An AFTER trigger is executed only after the triggering SQL statement has executed successfully. This successful execution includes all referential cascade actions and constraint checks associated with the object updated or deleted. An AFTER trigger will not recursively fire an INSTEAD OF trigger on the same table.