Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
dzirkelb
Yak Posting Veteran
53 Posts |
Posted - 2014-04-10 : 16:13:42
|
I just started looking into triggers for an issue I have. I want to replace every single 1/1/1900 date I have in my system with null, on an entire database level. I never want them, they cause multiple issues, and I need them gone.I have made a trigger on a test table that works for inserts, but not updates:CREATE TRIGGER UpdateDate ON test FOR INSERT ASIF EXISTS (select * FROM inserted WHERE CheckDate ='1/1/1900')BEGINUPDATE test SET CheckDate=NullFROM test T INNER JOIN inserted I ON T.AutoID=I.AutoIDWHERE I.CheckDate='1/1/1900'ENDAlso, it only works on this table, and the specific field mentioned.I am looking for a global, databse level trigger that will replace 1/1/1900 with null whenever it appears from an insert, or an update statement.Is this possible? |
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-04-10 : 16:42:39
|
You should instead use a check constraint that prevents that value. Add the constraint to each datetime column.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2014-04-10 : 17:27:47
|
quote: Originally posted by dzirkelb I am looking for a global, databse level trigger that will replace 1/1/1900 with null whenever it appears from an insert, or an update statement.Is this possible?
It is not. |
|
|
dzirkelb
Yak Posting Veteran
53 Posts |
Posted - 2014-04-11 : 08:57:57
|
The problem with constraints is I don't want the save or insert to fail, I just dont' want any 1/1/1900, just nulls or an actual date.Since I can not do it on an enterprise level, is it possible to do it on a schema level per table? Meaning, can I make one trigger to accommodate all date fields, without putting in the actual date field names? Then, I can easily copy and paste the trigger on all tables, and only need to change the table name. Otherwise, this will be a long project |
|
|
dzirkelb
Yak Posting Veteran
53 Posts |
Posted - 2014-04-11 : 10:26:51
|
From my above post, I wish to accommodate this query into the trigger somehow:SELECT DATA_TYPE, TABLE_NAME, COLUMN_NAMEFROM INFORMATION_SCHEMA.COLUMNS AS InformationSchemaWHERE (DATA_TYPE = 'datetime') AND (TABLE_NAME = 'test') OR (DATA_TYPE = 'smalldatetime') AND (TABLE_NAME = 'test')so all I don't have to create a trigger for all the date fields.In pseudo code, it would be as follows:CREATE TRIGGER UpdateDate ON test FOR INSERT AS*****insert my schema query here*****SELECT DATA_TYPE, TABLE_NAME, COLUMN_NAMEFROM INFORMATION_SCHEMA.COLUMNS AS InformationSchemaWHERE (DATA_TYPE = 'datetime') AND (TABLE_NAME = 'test') OR (DATA_TYPE = 'smalldatetime') AND (TABLE_NAME = 'test')******return results, loop through all results for the next part*****IF EXISTS (select * FROM inserted WHERE **COLUMN_NAME FROM ABOVE** ='1/1/1900')BEGINUPDATE test SET **COLUMN_NAME FROM ABOVE** =NullFROM test T INNER JOIN inserted I ON T.AutoID=I.AutoIDWHERE I.**COLUMN_NAME FROM ABOVE** ='1/1/1900'*****loop to next result from schema query****ENDAnyone think this is possible or understand what I'm asking? |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2014-04-11 : 11:34:57
|
The short answer, again, is no you cannot.The correct answer is to control access to your database with stored procedures so you can handle the logic there. If that is not an option, then you could add triggers to each table in question. But, even that is not a very good solution and just a hack. If you can't control the data getting into your system, then you might want to take a step back and see how to accomplish that (without triggers). |
|
|
|
|
|
|
|