1

I do not think what I am trying to do is possible, but I could not find any documentation on this. I have an INSTEAD OF UPDATE, INSERT, DELETE that always inserts data into a history table before performing the DML operation. So I created triggers that performed my insert into the history table and then I created 3 IF statements to figure out if the original operation was an INSERT UPDATE or DELETE operation to continue the original operation.

Is there a way to perform my history insert and then tell SQL Server to continue with the original INSERT, UPDATE or DELETE without writing those myself? See my code below. We are using the standard edition of SQL Server, so no CDC to help with logging changes. Thank you.

    SET NOCOUNT ON;

    -- ========================================================
    -- STEP 1: ARCHIVE. Get the records that are being inserted 
    -- or deleted and put them in the history table
    -- ========================================================

    -- INSERT INTO HISTORY TABLE HERE

    -- ========================================================
    -- STEP: CONTINUE. After archiving, perform the requested
    -- manipulation
    -- ========================================================

    IF(EXISTS(SELECT * FROM inserted) AND NOT EXISTS(SELECT * FROM deleted))
    BEGIN
      -- Perform insert into production table
    END

    IF(EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted))
    BEGIN
      -- Perform update on production table
    END

    IF(NOT EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted))
      -- Perform delete on production table    
END

3 Answers 3

2

Posting this as an answer to address the core question:

Is there a way to perform my history insert and then tell SQL Server to continue with the original INSERT, UPDATE or DELETE without writing those myself?

No, it is not possible to have an INSTEAD OF trigger just perform the original DML for you. This is why they are called INSTEAD OF and not BEFORE - they replace the original action with whatever logic you want to perform, which may or may not include DML against the original target table, depending on the scenario (you may want to prevent the insert instead of rolling it back, you may be migrating to a new table and using this as an interim solution, you may have distributed or otherwise not-directly-updateable views, etc).

If you feel you have a valid use case for INSTEAD OF, then you will have to manually code the DML they're replacing. I don't think you do, so, as the others have suggested, use an AFTER trigger.

4
  • Thank you, and that is what I expected to hear. I just wanted to makes sure I was not missing anything before I started rewriting the code.
    – jmzagorski
    Sep 22, 2014 at 13:52
  • I really wish it were possible, as in my case I only want to override an UPDATE if it meets certain criteria, otherwise the original UPDATE should be done. Would think that's a relatively common scenario? In my case the vast majority of UPDATEs will be one I can hard-code into the trigger, but some will be manually run, and the person running them will have to temporarily disable the trigger before running it, otherwise it will be discarded. Makes for a somewhat clunky solution... Apr 30, 2020 at 20:05
  • Actually, come to think of it, it should be possible to get the trigger to run the original statement by building it as a string dynamically using lots of UPDATE() tests and then executing that string. About as far from ideal as you can get in my book though. Apr 30, 2020 at 20:10
  • Um, yeah, never mind. Overriding an UPDATE only in certain conditions can of course be done with an AFTER UPDATE trigger which does a new update on the rows in question. May 6, 2020 at 8:26
1

You are defining an ordinary AFTER trigger. So use an AFTER trigger, not an INSTEAD OF trigger. The cases where you want to use 'instead of' triggers are, basically, none.

2
  • 2
    I don't understand your objection to instead of triggers. In cases where you will have a high rate of failure, you would rather write to a table then roll it back, with all the additional costs involved, than not do anything at all? I certainly agree that the OP's problem is not an ideal use case, but that doesn't mean there are zero use cases - even if you don't count the use case with views. Sep 22, 2014 at 13:46
  • I thought about that in the beginning but something with the old code and application were preventing me from doing that. Maybe it is time to revisit the AFTER trigger if that is the only way. Thank you
    – jmzagorski
    Sep 22, 2014 at 13:47
1

You could just use a FOR trigger instead of an INSTEAD OF trigger. The records, as they were before the DML statement, are still in the deleted table (for updates and deletes). Everything will still be contained in an implicit transaction.

In my opinion, INSTEAD OF triggers are really handy when you don't want to run the original DML, or on views.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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