Need help with a sql trigger
Posted on 2014-04-11
I have the need to, at the database level, replace every 1/1/1900 on all date fields with a null value, for every single field in our entire database. I understand I can't do a trigger at enterprise level, but instead, I have to create one on each table. I'm using sql server 2000.
However, each table has multiple date fields, and it would be too large of a project to manually create a trigger for every single date field. So, I'm looking to tap into the schema and do some sort of loop for the trigger, here is what I have in pseudo code:
CREATE TRIGGER UpdateDate ON test FOR INSERT AS
SELECT DATA_TYPE, TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS AS InformationSchema
WHERE (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')
UPDATE test SET **COLUMN_NAME FROM ABOVE** =Null
FROM test T INNER JOIN inserted I ON T.AutoID=I.AutoID
WHERE I.**COLUMN_NAME FROM ABOVE** ='1/1/1900'
*****loop to next result from schema query****
**COLUMN_NAME FROM ABOVE** would be whatever resulted from the schema query.
Anyone think this is possible or understand what I'm asking?