Creating Insert Triggers to Silently Ignore Data You Don’t Want

Say you’ve got an application that insists on inserting data into the database, and…you don’t want the data.

You want the application to THINK it inserted the data – you don’t want to roll it back or return an error to the end user. You just don’t want the data, and you don’t want the hassle of deleting it later.

Here’s our imaginary table Documents:

We want to ignore all documents with a CreationDate prior to 2018. Enter our new friend the INSTEAD OF trigger:

The SET NOCOUNT ON statement is important here because it hides the “1 row(s) affected” message that would normally come out of the trigger.

Now, when we insert two rows – one before 2018, and one after – they both work:

Our application thinks they were both inserted:

2 rows enter, 1 row leaves

But if I select the data out of the table:

There can be only one

This is just a starting point: you still need to communicate this to the dev team, and handle updates (because the app could set the CreationDate to an earlier value,) and deal with bulk inserts (where triggers are ignored by default.) It’s just something that I needed for a project, and I figured you might get a chuckle out of it too.

Previous Post
Using LIKE on Integers Gets You Implicit Conversion
Next Post
The Annals of Hilariously Bad Code, Part 2

17 Comments. Leave new

  • Like the stealthiness!

    Reply
  • Just curious, why the trigger instead of validating before insert?

    Reply
    • Ortolan – sure, if you’d code it a different way, definitely give that a shot. I just banged this out really fast to get the job done – you can always come up with new and better ways to do stuff.

      Reply
  • What is the performance cost of this method?

    Reply
  • TWO awesome movie references!

    Reply
  • Nasty little landmine there…
    Now, everytime you ALTER TABLE ADD COLUMN, you have to remember to ALTER TRIGGER too. Otherwise you get a beautiful NULL is your new column (or an error if NOT NULL). So you insert a record, carefully check that your INSERT statement is providing all columns only to find that the value is not being inserted, and you don’t know why.
    Been bitten by this one already… don’t ask, some inherited legacy db full of those undocumented ‘features’.

    Reply
  • Slashes?

    I’m not going to be the one that posts a link to the ISO 8601 xkcd. I just want you to know that the thought was there.

    Reply
    • Hahaha, nice. Listen, you should just be happy I got yyyy/mm/dd right – it took me years to switch over from the American formats, hahaha.

      Reply
    • Paul D. Samsig
      February 9, 2018 5:10 pm

      There is only one language portable way to write dates in SQL Server (and ISO 8601 is not it)

      — First two will fail

      SET LANGUAGE ‘German’
      SELECT CONVERT(DATETIME, ‘2018/12/13’)
      SET LANGUAGE ‘German’
      SELECT CONVERT(DATETIME, ‘2018-12-13’)
      GO

      — All these will pass

      SET LANGUAGE ‘German’
      SELECT CONVERT(DATETIME, ‘20181213’)
      SET LANGUAGE ‘English’
      SELECT CONVERT(DATETIME, ‘20181213’)
      SET LANGUAGE ‘French’
      SELECT CONVERT(DATETIME, ‘20181213’)
      SET LANGUAGE ‘Japanese’
      SELECT CONVERT(DATETIME, ‘20181213’)
      SET LANGUAGE ‘Danish’
      SELECT CONVERT(DATETIME, ‘20181213’)
      SET LANGUAGE ‘Spanish’
      SELECT CONVERT(DATETIME, ‘20181213’)
      SET LANGUAGE ‘Italian’
      SELECT CONVERT(DATETIME, ‘20181213’)
      SET LANGUAGE ‘Dutch’
      SELECT CONVERT(DATETIME, ‘20181213’)
      SET LANGUAGE ‘Norwegian’
      SELECT CONVERT(DATETIME, ‘20181213’)
      SET LANGUAGE ‘Portuguese’
      SELECT CONVERT(DATETIME, ‘20181213’)
      SET LANGUAGE ‘Finnish’
      SELECT CONVERT(DATETIME, ‘20181213’)
      SET LANGUAGE ‘Swedish’
      SELECT CONVERT(DATETIME, ‘20181213’)
      SET LANGUAGE ‘Czech’
      SELECT CONVERT(DATETIME, ‘20181213’)
      SET LANGUAGE ‘Hungarian’
      SELECT CONVERT(DATETIME, ‘20181213’)
      SET LANGUAGE ‘Polish’
      SELECT CONVERT(DATETIME, ‘20181213’)
      SET LANGUAGE ‘Romanian’
      SELECT CONVERT(DATETIME, ‘20181213’)
      SET LANGUAGE ‘Croatian’
      SELECT CONVERT(DATETIME, ‘20181213’)
      SET LANGUAGE ‘Slovak’
      SELECT CONVERT(DATETIME, ‘20181213’)
      SET LANGUAGE ‘Greek’
      SELECT CONVERT(DATETIME, ‘20181213’)
      SET LANGUAGE ‘Bulgarian’
      SELECT CONVERT(DATETIME, ‘20181213’)
      SET LANGUAGE ‘Russian’
      SELECT CONVERT(DATETIME, ‘20181213’)
      SET LANGUAGE ‘Turkish’
      SELECT CONVERT(DATETIME, ‘20181213’)
      SET LANGUAGE ‘British English’
      SELECT CONVERT(DATETIME, ‘20181213’)
      SET LANGUAGE ‘Estonian’
      SELECT CONVERT(DATETIME, ‘20181213’)
      SET LANGUAGE ‘Latvian’
      SELECT CONVERT(DATETIME, ‘20181213’)
      SET LANGUAGE ‘Lithuanian’
      SELECT CONVERT(DATETIME, ‘20181213’)
      SET LANGUAGE ‘Brazilian’
      SELECT CONVERT(DATETIME, ‘20181213’)
      SET LANGUAGE ‘Traditional Chinese’
      SELECT CONVERT(DATETIME, ‘20181213’)
      SET LANGUAGE ‘Korean’
      SELECT CONVERT(DATETIME, ‘20181213’)
      SET LANGUAGE ‘Simplified Chinese’
      SELECT CONVERT(DATETIME, ‘20181213’)
      SET LANGUAGE ‘Arabic’
      SELECT CONVERT(DATETIME, ‘20181213’)
      SET LANGUAGE ‘Thai’
      SELECT CONVERT(DATETIME, ‘20181213’)
      GO

      Reply
      • There is another “universal” format, which will be converted true:
        SELECT CONVERT(DATETIME, ‘2018-12-13T14:23:25’)
        will work for all the languages too (be aware of the letter T instead of the space between date and time)

        PS:
        when you want to specify a time, you’ll have to use either the format in my example or from the post above with a space as separator:
        SELECT CONVERT(DATETIME, ‘20181213 14:23:25’)

        Reply
  • Well, Brent, once again, I just don’t like you at all.

    I read this a few days ago and thought “I can’t imagine being in that kind of crappy situation where you can’t just fix the actual problem. What a terrible solution.”

    Today, I had a need to pull folder names from a file system and just dump them into a table- there are millions and we just need a quick utility to validate that some exist- and the file system is just terrible at this when you’re dealing with millions of nested folders.

    Simple enough, I create a really narrow table, cluster on the path stored into a varchar, and then I run the magic command- insert into tbl_folder (folderpath) exec xp_cmdshell ‘dir d:\basefolder\ /b /ad /s’

    That should work, except a few minutes later, the error returns: cannot insert null value. Who knows why that command is giving null value and I don’t care why (probably the footer at the bottom, which I know I can turn off with another dir switch). I just want to get the non-null rows into the table as fast as possible, and I can’t just “allow nulls” on the field because it’s an indexed field- that’s the whole point. I guess I move the clustered index, allow nulls, to the xp_cmdshell dir, delete null records, move the clustered index back and wait for it to sort through 2.5 million records after the fact…

    Then I remember, I can just use a trigger to silently ignore the nulls and go on.

    insert into dbo.tbl_folder (folderpath) select folderpath from inserted where folderpath is not null

    Man, I really don’t like you. Always being so nice and sharing your gigantic brain and giving free tips and coming up with solutions to my problems two weeks before I even know I have problems.

    Sigh.

    Keep up the good work making me hate you and your stupid cool haircut. 😉

    Reply
    • Jeremy – I know exactly what you mean! It’s so funny – I have to do something awful (like discarding inserts) and so I document what I’m doing, and the whole time, I feel dirty about it. I shouldn’t be writing that code, and yet…I have to, so…somebody else probably has to, too! Heh, glad I could help.

      And all credit for the haircut goes to my wife for making me go to Vidal Sassoon just once to try it out, heh. I was shocked at what a difference it made!

      Reply
  • argh – I really hate when webpages refresh when you are trying to ‘Leave a Reply’! Anyway… for the second time… We use the ‘Registered Servers’ trick across (I guess) around 50 of our servers for a given line of business – ha and that’s not even close to all of them! Great idea but it still requires my intervention… I am looking for a an answer to a more specific question. Can i script it out instead of using the SSMS UI for Registered Servers w/o going outside of a ‘t-sql sproc environment’. While I can and often do, I need something simple instead of relying on (PowerShell or vbs or vba or NT or a dozen other platform solutions that i can think of) and I’m too old and lazy to read thru all the previous comments on this topic. Sooo pretty please, let me know?!? And Thanks!

    Reply
  • er

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}