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.
Best Answer
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 anAFTER
trigger. (In fact it can have severalAFTER
triggers.)https://msdn.microsoft.com/en-us/library/ms189799.aspx outlines some rules, including:
INSTEAD OF
trigger execution and before theAFTER
trigger execution. If the constraints are violated, theINSTEAD OF
trigger actions are rolled back and theAFTER
trigger is not fired.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. AnAFTER
trigger will not recursively fire anINSTEAD OF
trigger on the same table.