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:
|
1 2 3 4 |
CREATE TABLE dbo.Documents (DocumentName VARCHAR(50), CreationDate DATETIME); GO |
We want to ignore all documents with a CreationDate prior to 2018. Enter our new friend the INSTEAD OF trigger:
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE OR ALTER TRIGGER IgnoreOldDocuments ON dbo.Documents INSTEAD OF INSERT AS BEGIN SET NOCOUNT ON; INSERT dbo.Documents(DocumentName, CreationDate) SELECT DocumentName, CreationDate FROM inserted WHERE CreationDate >= '2018/01/01'; END GO |
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:
|
1 2 3 4 5 |
INSERT INTO dbo.Documents (DocumentName, CreationDate) VALUES ('December 2017', '2017/12/01'); INSERT INTO dbo.Documents (DocumentName, CreationDate) VALUES ('January 2018', '2018/12/01'); GO |
Our application thinks they were both inserted:
But if I select the data out of the table:
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.
19 Comments. Leave new
Like the stealthiness!
Just curious, why the trigger instead of validating before insert?
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.
What is the performance cost of this method?
Zafer – for me, it was way less than the cost of accepting the rows, and then deleting them later.
TWO awesome movie references!
Thanks for pointing those out! I knew the HL one, but not the MM one 🙂
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’.
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.
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.
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
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’)
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. 😉
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!
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!
Mark – for unrelated questions, go ahead and hit a Q&A site like https://dba.stackexchange.com. Brent
er
We have a web site logging 300000+ entries per hour but we only want about 15% of those. This would be a great solution for us rather than having to make multiple changes to the site.
How do I get this to work when insert is called from within .net? The instead of insert triggers when insert is executed in SSMS but from .net the insert (via sp_executesql) doesn’t work – more like no insert ever happened.
Troubleshooting code is a little beyond what I can do in a blog comment, unfortunately. You can contact me for consulting help, or post a question with your code at https://stackoverflow.com.