0

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.

  • Why not just use a Foreign Key? And if the first trigger prevents a row from being inserted, then in what use case would the AFTER trigger ever be needed? – LowlyDBA Mar 25 '15 at 17:24
  • Agreed with John - how is it possible that the INSTEAD OF trigger would succeed and allow the insert but the AFTER trigger would roll it back? How could a check pass and then an instant later fail? – Aaron Bertrand Mar 25 '15 at 17:48
  • @John we are restricted to put any such constraint on DB from admin. – Niraj Mar 25 '15 at 17:57
  • @Aaron I didn't mean to implement both triggers, I have to implement only one. Both instead of and After trigger are able to solve my problem. can you suggest me which one to use as a best practise,in this scenario? – Niraj Mar 25 '15 at 17:58
  • 1
    @Niraj So, are you saying that your admin will not allow a Foreign key, but will allow a trigger that prevents inserts? Have you actually discussed this with her/him? – Mister Magoo Mar 26 '15 at 1:20
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.