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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Way to create an entire database trigger

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 AS
IF EXISTS (select * FROM inserted WHERE CheckDate ='1/1/1900')
BEGIN
UPDATE test SET CheckDate=Null
FROM test T INNER JOIN inserted I ON T.AutoID=I.AutoID
WHERE I.CheckDate='1/1/1900'
END


Also, 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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page

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_NAME
FROM INFORMATION_SCHEMA.COLUMNS AS InformationSchema
WHERE (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_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')
BEGIN
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****
END


Anyone think this is possible or understand what I'm asking?
Go to Top of Page

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).
Go to Top of Page
   

- Advertisement -