Need help with a sql trigger

Why do you want a trigger to fire on a single table that updates every table and sets all date fields to null when the value is 1/1/1900?

Surely this is a one off update rather than something you want to keep doing? Regardless, it shouldn't be a trigger. Maybe a SQL job once a day but, again, why do you need to do this?

This trigger won't update all tables, it will only update the table it is set to, for this example, table "test", and all the date fields associated with table "test".

1/1/1900 causes issues with a lot of programming on our systems, but more importantly, the execs are asking it to be removed cause it causes "questions" from people.

I've coded many pages / reports to simply not display 1/1/1900, and on the front end when saving to save a null instead; however, our spiderweb of a system is just too vast to accommodate them all, so I want to do it at the table level.

You'll need to do both.

First, you'll need to put a trigger in place to insure that future INSERTs/UPDATEs put in NULL instead of 1/1/1900.

Second, you'll need to run UPDATE on the table(s) to change all existing dates of 1/1/1900 to NULL.


Q: Once you decide to change dates on a table, will you always change all dates, or do you need the capability to include/exclude specific date column(s)?

I will always change all dates to null, 1/1/1900 has done nothing but cause headaches for us, mainly because the non IT people can't grasp what 1/1/1900 is compared to null, so, to avoid confusion, make it null.

I do understand I'll have to do an update on my entire database of all date fields where the date is 1/1/1900 to null.  I'll tackle that after I get the trigger in place.

But, back to the trigger in question, because I need to put it on each table, and it needs to affect all fields within that table, can my logic from above be put into code so all I have to change when copying the trigger from table to table is the table name?

Just generate the trigger(s) for all table(s) at once.  You don't want to do any copy/paste on something like this, it just makes room for errors :-)

I'll code up a trigger gen as soon as I can.

That would be awesome, and beyond the scope of my brain :)

Below is a pretty good start I think.

The only "cheat" still in place is assuming the table has an identity column.  Naturally not every table does, so the code needs changed to use something else if an identity column is not available. [Note that when an identity column is present, "$IDENTITY" is a valid column name and does not need to be changed to the actual column name.]


DECLARE @table_name_pattern sysname
DECLARE @system_type_ids TABLE (
    system_type_id tinyint PRIMARY KEY
    )
DECLARE @trigger_code_template nvarchar(max)

SET @table_name_pattern = '%' --%=all, adjust as preferred
INSERT INTO @system_type_ids
    SELECT system_type_id
    FROM sys.types
    WHERE
        name IN ('date', 'datetime', 'smalldatetime', 'datetime2', 'datetimeoffset') --adjust as preferred
SET @trigger_code_template = '
CREATE TRIGGER $table$__TRG_Adjust_Dates
ON [$schema$].[$table$]
AFTER INSERT, UPDATE
AS
SET NOCOUNT ON;
UPDATE t
SET $columns_update$
FROM [$schema$].[$table$] t
INNER JOIN inserted i ON
    i.$IDENTITY = t.$IDENTITY  /* CHEAT! :-) */
;
GO
'

------------------------------------------------------------------------------------------------------------------------

DECLARE @schema_id int
DECLARE @table_name sysname
DECLARE @object_id int
DECLARE @columns_update varchar(max)

DECLARE tables_cursor CURSOR FAST_FORWARD LOCAL FOR
SELECT t.schema_id, t.name, t.object_id
FROM sys.tables t
WHERE
    t.name LIKE @table_name_pattern AND
    EXISTS(
        SELECT 1
        FROM sys.columns c
        WHERE
            c.object_id = t.object_id AND
            c.is_computed = 0 AND            
            c.system_type_id IN (SELECT system_type_id FROM @system_type_ids)
    )            

OPEN tables_cursor

SET NOCOUNT ON
WHILE 1 = 1
BEGIN
    FETCH NEXT FROM tables_cursor INTO @schema_id, @table_name, @object_id
    IF @@FETCH_STATUS = -1
        BREAK
    IF @@FETCH_STATUS = -2
        CONTINUE
    SELECT @columns_update = SUBSTRING((SELECT
            ',[' + CAST(c.name AS varchar(128)) + '] = ' + 'CASE WHEN ' +
            '[' + CAST(c.name AS varchar(128)) + '] = ''19000101'' THEN NULL ELSE ' +
            '[' + CAST(c.name AS varchar(128)) + '] END'
        FROM sys.columns c
        WHERE
            c.object_id = @object_id AND
            OBJECTPROPERTYEX(c.object_id, 'IsUserTable') = 1 AND
            c.is_computed = 0 AND
            c.system_type_id IN (
                SELECT system_type_id
                FROM sys.types
                WHERE
                    name IN ('date', 'datetime', 'smalldatetime', 'datetime2', 'datetimeoffset') --adjust as preferred
                )
        FOR XML PATH(''))
        , 2, 1000000)
    SELECT REPLACE(REPLACE(REPLACE(@trigger_code_template,
        '$schema$', SCHEMA_NAME(@schema_id)),
        '$table$', @table_name),
        '$columns_update$', @columns_update) AS [--sql_trigger_ddl]
END --WHILE
SET NOCOUNT OFF

CLOSE tables_cursor

that looks complex.

I'll add a primary key to all tables that don't have one, that's just good practice anyway.

This is too complex for me to touch, however.  So, let's assume that the date schema you have is good (date, datetime, smalldatetime, datetime2, datetimeoffset), and assume all tables have a primary key.

After that, could you give me something I can just copy and paste direct?  Then, that begs the question, where the heck do I copy and paste this monster to and run it at?

Finally, to confirm, this will add a trigger that replaces all future insert and updates of any form of date field from 1/1/1900 to null, correct?

We could put the code in a proc and all you'd have to run is the proc, which hides the complexity (one huge advantage of stored procs in general).

The code currently just displays the trigger code, but it could be changed to actually execute it as well.


>> assume all tables have a primary key. <<
That would still need to be added into my code.  Any unique index will do, but I don't have time right now to add that code.

You could use the above to gen the triggers and adjust the join code yourself.  That would still be much easier than copying and pasting the entire trigger.


>> where the heck do I copy and paste this monster to and run it at? <<
Into a query window in SSMS.  Just make sure you are in the database you want to gen triggers for.


>> this will add a trigger that replaces all future insert and updates of any form of date field from 1/1/1900 to null, correct? <<
It generates code to do that, yes.  It doesn't currently run it because the join logic is not complete.

I copied and pasted the above into a query and it produced the following errors:

Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near 'max'.
Msg 137, Level 15, State 1, Line 30
Must declare the variable '@trigger_code_template'.
Msg 170, Level 15, State 1, Line 33
Line 33: Incorrect syntax near 'max'.
Msg 170, Level 15, State 1, Line 74
Line 74: Incorrect syntax near 'XML'.
Msg 137, Level 15, State 2, Line 76
Must declare the variable '@trigger_code_template'.

I'm sure I'm doing something wrong or not doing something I should.  All I did was copy the exact code into a new query on the database I wanted to use, hit parse, and that was the results.

Refined version below.  After the joins are corrected, you just run my code, copy its output to a new window, and run it to (re)create all triggers.

SET NOCOUNT ON

DECLARE @table_name_pattern sysname
DECLARE @system_type_ids TABLE (
    system_type_id tinyint PRIMARY KEY
    )
DECLARE @trigger_code_template nvarchar(max)

SET @table_name_pattern = '%'
INSERT INTO @system_type_ids
    SELECT system_type_id
    FROM sys.types
    WHERE
        name IN ('date', 'datetime', 'smalldatetime', 'datetime2', 'datetimeoffset') --adjust as preferred
SET @trigger_code_template = '
CREATE TRIGGER $trigger_name$
ON [$schema$].[$table$]
AFTER INSERT, UPDATE
AS
SET NOCOUNT ON;
UPDATE t
SET $columns_update$
FROM [$schema$].[$table$] t
INNER JOIN inserted i ON
    i.$IDENTITY = t.$IDENTITY
GO
'

------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID('tempdb..#sql') IS NOT NULL
    DROP TABLE #sql
CREATE TABLE #sql (
    id int IDENTITY(1, 1) NOT NULL,
    table_name varchar(128) NOT NULL,
    trigger_code nvarchar(max)
    )

DECLARE @trigger_name_pattern sysname
DECLARE @schema_id int
DECLARE @table_name sysname
DECLARE @object_id int
DECLARE @columns_update varchar(max)
DECLARE @trigger_count int

SET @trigger_name_pattern = '$table$__TRG_Adjust_Dates'

DECLARE tables_cursor CURSOR FAST_FORWARD LOCAL FOR
SELECT t.schema_id, t.name, t.object_id
FROM sys.tables t
WHERE
    t.name LIKE @table_name_pattern AND
    EXISTS(
        SELECT 1
        FROM sys.columns c
        WHERE
            c.object_id = t.object_id AND
            c.is_computed = 0 AND            
            c.system_type_id IN (SELECT system_type_id FROM @system_type_ids)
    )            

OPEN tables_cursor

SET @trigger_count = 0
WHILE 1 = 1
BEGIN
    FETCH NEXT FROM tables_cursor INTO @schema_id, @table_name, @object_id
    IF @@FETCH_STATUS = -1
        BREAK
    IF @@FETCH_STATUS = -2
        CONTINUE
    SELECT @columns_update = SUBSTRING((SELECT
            ',[' + CAST(c.name AS varchar(128)) + '] = ' + 'CASE WHEN ' + 
            '[' + CAST(c.name AS varchar(128)) + '] = ''19000101'' THEN NULL ELSE ' +
            '[' + CAST(c.name AS varchar(128)) + '] END'
        FROM sys.columns c
        WHERE
            c.object_id = @object_id AND
            OBJECTPROPERTYEX(c.object_id, 'IsUserTable') = 1 AND
            c.is_computed = 0 AND
            c.system_type_id IN (
                SELECT system_type_id
                FROM sys.types
                WHERE
                    name IN ('date', 'datetime', 'smalldatetime', 'datetime2', 'datetimeoffset') --adjust as preferred
                )
        FOR XML PATH(''))
        , 2, 1000000)
    INSERT INTO #sql ( table_name, trigger_code )
    SELECT
        @table_name,
        'IF OBJECT_ID(''' + REPLACE(@trigger_name_pattern, '$table$', @table_name) + ''') IS NOT NULL' + CHAR(10) +
        '    DROP TRIGGER ' + REPLACE(@trigger_name_pattern, '$table$', @table_name) + CHAR(10) +
        'GO '    
    INSERT INTO #sql ( table_name, trigger_code )
    SELECT
        @table_name,
        REPLACE(REPLACE(REPLACE(REPLACE(@trigger_code_template,
            '$trigger_name$', REPLACE(@trigger_name_pattern, '$table$', @table_name)),
            '$schema$', SCHEMA_NAME(@schema_id)),
            '$table$', @table_name),
            '$columns_update$', @columns_update) AS [--trigger_code]
    SET @trigger_count = @trigger_count + 1
END --WHILE

CLOSE tables_cursor

SELECT trigger_code AS [--generated_create_trigger_code]
FROM #sql
ORDER BY
    table_name, id

SELECT '--Total triggers generated: ' + CAST(@trigger_count AS varchar(10))

SET NOCOUNT OFF

Select allOpen in new window

Please try to copy and paste the code from my immediately previous post.  Let me know if it doesn't run.

Server: Msg 170, Level 15, State 1, Line 7
Line 7: Incorrect syntax near 'max'.
Server: Msg 105, Level 15, State 1, Line 15
Unclosed quotation mark before the character string '
CREATE TRIGGER $trigger_name$
ON [$schema$].[$table$]
AFTER INSERT, UPDATE
AS
SET NOCOUNT ON;
UPDATE t
SET $columns_update$
FROM [$schema$].[$table$] t
INNER JOIN inserted i ON
    i.$IDENTITY = t.$IDENTITY
'.
Server: Msg 170, Level 15, State 1, Line 15
Line 15: Incorrect syntax near '
CREATE TRIGGER $trigger_name$
ON [$schema$].[$table$]
AFTER INSERT, UPDATE
AS
SET NOCOUNT ON;
UPDATE t
SET $columns_upda'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '

------------------------------------------------------------------------------------------------------------------------

'.
Server: Msg 170, Level 15, State 1, Line 66
Line 66: Incorrect syntax near '$'.

Cripes, sorry, you can't use MAX since you are on SQL 2000.


SET NOCOUNT ON

DECLARE @table_name_pattern sysname
DECLARE @system_type_ids TABLE (
    system_type_id tinyint PRIMARY KEY
    )
DECLARE @trigger_code_template nvarchar(4000)

SET @table_name_pattern = '%'
INSERT INTO @system_type_ids
    SELECT system_type_id
    FROM sys.types
    WHERE
        name IN ('date', 'datetime', 'smalldatetime', 'datetime2', 'datetimeoffset') --adjust as preferred
SET @trigger_code_template = '
CREATE TRIGGER $trigger_name$
ON [$schema$].[$table$]
AFTER INSERT, UPDATE
AS
SET NOCOUNT ON;
UPDATE t
SET $columns_update$
FROM [$schema$].[$table$] t
INNER JOIN inserted i ON
    i.$IDENTITY = t.$IDENTITY
GO
'

------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID('tempdb..#sql') IS NOT NULL
    DROP TABLE #sql
CREATE TABLE #sql (
    id int IDENTITY(1, 1) NOT NULL,
    table_name varchar(128) NOT NULL,
    trigger_code nvarchar(4000)
    )

DECLARE @trigger_name_pattern sysname
DECLARE @schema_id int
DECLARE @table_name sysname
DECLARE @object_id int
DECLARE @columns_update nvarchar(4000)
DECLARE @trigger_count int

SET @trigger_name_pattern = '$table$__TRG_Adjust_Dates'

DECLARE tables_cursor CURSOR FAST_FORWARD LOCAL FOR
SELECT t.schema_id, t.name, t.object_id
FROM sys.tables t
WHERE
    t.name LIKE @table_name_pattern AND
    EXISTS(
        SELECT 1
        FROM sys.columns c
        WHERE
            c.object_id = t.object_id AND
            c.is_computed = 0 AND            
            c.system_type_id IN (SELECT system_type_id FROM @system_type_ids)
    )            

OPEN tables_cursor

SET @trigger_count = 0
WHILE 1 = 1
BEGIN
    FETCH NEXT FROM tables_cursor INTO @schema_id, @table_name, @object_id
    IF @@FETCH_STATUS = -1
        BREAK
    IF @@FETCH_STATUS = -2
        CONTINUE
    SELECT @columns_update = SUBSTRING((SELECT
            ',[' + CAST(c.name AS varchar(128)) + '] = ' + 'CASE WHEN ' +
            '[' + CAST(c.name AS varchar(128)) + '] = ''19000101'' THEN NULL ELSE ' +
            '[' + CAST(c.name AS varchar(128)) + '] END'
        FROM sys.columns c
        WHERE
            c.object_id = @object_id AND
            OBJECTPROPERTYEX(c.object_id, 'IsUserTable') = 1 AND
            c.is_computed = 0 AND
            c.system_type_id IN (
                SELECT system_type_id
                FROM sys.types
                WHERE
                    name IN ('date', 'datetime', 'smalldatetime', 'datetime2', 'datetimeoffset') --adjust as preferred
                )
        FOR XML PATH(''))
        , 2, 1000000)
    INSERT INTO #sql ( table_name, trigger_code )
    SELECT
        @table_name,
        'IF OBJECT_ID(''' + REPLACE(@trigger_name_pattern, '$table$', @table_name) + ''') IS NOT NULL' + CHAR(10) +
        '    DROP TRIGGER ' + REPLACE(@trigger_name_pattern, '$table$', @table_name) + CHAR(10) +
        'GO '    
    INSERT INTO #sql ( table_name, trigger_code )
    SELECT
        @table_name,
        REPLACE(REPLACE(REPLACE(REPLACE(@trigger_code_template,
            '$trigger_name$', REPLACE(@trigger_name_pattern, '$table$', @table_name)),
            '$schema$', SCHEMA_NAME(@schema_id)),
            '$table$', @table_name),
            '$columns_update$', @columns_update) AS [--trigger_code]
    SET @trigger_count = @trigger_count + 1
END --WHILE

CLOSE tables_cursor

SELECT trigger_code AS [--generated_create_trigger_code]
FROM #sql
ORDER BY
    table_name, id

SELECT '--Total triggers generated: ' + CAST(@trigger_count AS varchar(10))

SET NOCOUNT OFF

Getting closer:

Msg 170, Level 15, State 1, Line 87
Line 87: Incorrect syntax near 'XML'.
Msg 195, Level 15, State 10, Line 100
'SCHEMA_NAME' is not a recognized function name.

I don't remember how schema names were provided in SQL 2000.  Therefore, I've hard-coded 'dbo'.  If that's not right, change it.


SET NOCOUNT ON

DECLARE @table_name_pattern sysname
DECLARE @xtypes TABLE (
    xtype tinyint PRIMARY KEY
    )
DECLARE @trigger_code_template nvarchar(4000) --chg all to MAX!

SET @table_name_pattern = '%'
INSERT INTO @xtypes
    SELECT xtype
    FROM systypes
    WHERE
        name IN ('date', 'datetime', 'smalldatetime', 'datetime2', 'datetimeoffset') --adjust as preferred
SET @trigger_code_template = '
CREATE TRIGGER $trigger_name$
ON [$schema$].[$table$]
AFTER INSERT, UPDATE
AS
SET NOCOUNT ON;
UPDATE t
SET $columns_update$
FROM [$schema$].[$table$] t
/* you must correct this join before using the trigger */
INNER JOIN inserted i ON
    i.id = t.id
GO
'

------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID('tempdb..#sql') IS NOT NULL
    DROP TABLE #sql
CREATE TABLE #sql (
    id int IDENTITY(1, 1) NOT NULL,
    table_name varchar(128) NOT NULL,
    trigger_code nvarchar(4000)
    )

DECLARE @trigger_name_pattern sysname
DECLARE @schema_id int
DECLARE @table_name sysname
DECLARE @object_id int
DECLARE @columns_update nvarchar(4000)
DECLARE @trigger_count int

SET @trigger_name_pattern = '$table$__TRG_Adjust_Dates'

DECLARE tables_cursor CURSOR FAST_FORWARD LOCAL FOR
SELECT 0, t.name, t.id
FROM sysobjects t
WHERE
    t.name LIKE @table_name_pattern AND
    t.type = 'U' AND
    EXISTS(
        SELECT 1
        FROM syscolumns c
        WHERE
            c.id = t.id AND
            c.xtype IN (SELECT xtype FROM @xtypes)
    )            

OPEN tables_cursor

SET @trigger_count = 0
WHILE 1 = 1
BEGIN
    FETCH NEXT FROM tables_cursor INTO @schema_id, @table_name, @object_id
    IF @@FETCH_STATUS = -1
        BREAK
    IF @@FETCH_STATUS = -2
        CONTINUE
    SET @columns_update = ''
    SELECT @columns_update = @columns_update +
            ',[' + CAST(c.name AS varchar(128)) + '] = ' + 'CASE WHEN ' +
            '[' + CAST(c.name AS varchar(128)) + '] = ''19000101'' THEN NULL ELSE ' +
            '[' + CAST(c.name AS varchar(128)) + '] END'
        FROM syscolumns c
        WHERE
            c.id = @object_id AND
            OBJECTPROPERTY(c.id, 'IsUserTable') = 1 AND
            c.xtype IN (
                SELECT xtype
                FROM systypes
                WHERE
                    name IN ('date', 'datetime', 'smalldatetime', 'datetime2', 'datetimeoffset') --adjust as preferred
                )
    SET @columns_update = SUBSTRING(@columns_update, 2, 8000)
    INSERT INTO #sql ( table_name, trigger_code )
    SELECT
        @table_name,
        'IF OBJECT_ID(''' + REPLACE(@trigger_name_pattern, '$table$', @table_name) + ''') IS NOT NULL' + CHAR(10) +
        '    DROP TRIGGER ' + REPLACE(@trigger_name_pattern, '$table$', @table_name) + CHAR(10) +
        'GO '    
    INSERT INTO #sql ( table_name, trigger_code )
    SELECT
        @table_name,
        REPLACE(REPLACE(REPLACE(REPLACE(@trigger_code_template,
            '$trigger_name$', REPLACE(@trigger_name_pattern, '$table$', @table_name)),
            '$schema$', 'dbo'),
            '$table$', @table_name),
            '$columns_update$', @columns_update) AS [--trigger_code]
    SET @trigger_count = @trigger_count + 1
END --WHILE

CLOSE tables_cursor

SELECT trigger_code AS [--generated_create_trigger_code]
FROM #sql
ORDER BY
    table_name, id

SELECT '--Total triggers generated: ' + CAST(@trigger_count AS varchar(10))

SET NOCOUNT OFF

Oh my, my favorite sql limitation:

Warning: The table '#sql' has been created but its maximum row size (8157) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
Msg 212, Level 16, State 1, Line 88
Expression result length exceeds the maximum. 8000 max, 16000 found.

Grrr, you may have to exclude any table with > 40(?) date columns.

For now, I've changed all nvarchar to varchar.

Can't imagine 16000 bytes -- how many datetime columns do you have in one table??


SET NOCOUNT ON

DECLARE @table_name_pattern varchar(128)
CREATE TABLE #xtypes (
    xtype tinyint PRIMARY KEY
    )
DECLARE @trigger_code_template varchar(8000)

SET @table_name_pattern = '%'
INSERT INTO @xtypes
    SELECT xtype
    FROM systypes
    WHERE
        name IN ('date', 'datetime', 'smalldatetime', 'datetime2', 'datetimeoffset') --adjust as preferred
SET @trigger_code_template = '
CREATE TRIGGER $trigger_name$
ON [$schema$].[$table$]
AFTER INSERT, UPDATE
AS
SET NOCOUNT ON;
UPDATE t
SET $columns_update$
FROM [$schema$].[$table$] t
/* you must correct this join before using the trigger */
INNER JOIN inserted i ON
    i.id = t.id
GO
'

------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID('tempdb..#sql') IS NOT NULL
    DROP TABLE #sql
CREATE TABLE #sql (
    id int IDENTITY(1, 1) NOT NULL,
    table_name varchar(128) NOT NULL,
    trigger_code varchar(8000)
    )

DECLARE @trigger_name_pattern sysname
DECLARE @schema_id int
DECLARE @table_name sysname
DECLARE @object_id int
DECLARE @columns_update varchar(8000)
DECLARE @trigger_count int

SET @trigger_name_pattern = '$table$__TRG_Adjust_Dates'

DECLARE tables_cursor CURSOR FAST_FORWARD LOCAL FOR
SELECT 0, t.name, t.id
FROM sysobjects t
WHERE
    t.name LIKE @table_name_pattern AND
    t.type = 'U' AND
    40 <=
    EXISTS(
        SELECT COUNT(*)
        FROM syscolumns c
        WHERE
            c.id = t.id AND
            c.xtype IN (SELECT xtype FROM #xtypes)
    )            

OPEN tables_cursor

SET @trigger_count = 0
WHILE 1 = 1
BEGIN
    FETCH NEXT FROM tables_cursor INTO @schema_id, @table_name, @object_id
    IF @@FETCH_STATUS = -1
        BREAK
    IF @@FETCH_STATUS = -2
        CONTINUE
    SET @columns_update = ''
    SELECT @columns_update = @columns_update +
            ',[' + CAST(c.name AS varchar(128)) + '] = ' + 'CASE WHEN ' +
            '[' + CAST(c.name AS varchar(128)) + '] = ''19000101'' THEN NULL ELSE ' +
            '[' + CAST(c.name AS varchar(128)) + '] END'
        FROM syscolumns c
        WHERE
            c.id = @object_id AND
            OBJECTPROPERTY(c.id, 'IsUserTable') = 1 AND
            c.xtype IN (
                SELECT xtype
                FROM systypes
                WHERE
                    name IN ('date', 'datetime', 'smalldatetime', 'datetime2', 'datetimeoffset') --adjust as preferred
                )
    SET @columns_update = SUBSTRING(@columns_update, 2, 8000)
    INSERT INTO #sql ( table_name, trigger_code )
    SELECT
        @table_name,
        'IF OBJECT_ID(''' + REPLACE(@trigger_name_pattern, '$table$', @table_name) + ''') IS NOT NULL' + CHAR(10) +
        '    DROP TRIGGER ' + REPLACE(@trigger_name_pattern, '$table$', @table_name) + CHAR(10) +
        'GO '    
    INSERT INTO #sql ( table_name, trigger_code )
    SELECT
        @table_name,
        REPLACE(REPLACE(REPLACE(REPLACE(@trigger_code_template,
            '$trigger_name$', REPLACE(@trigger_name_pattern, '$table$', @table_name)),
            '$schema$', 'dbo'),
            '$table$', @table_name),
            '$columns_update$', @columns_update) AS [--trigger_code]
    SET @trigger_count = @trigger_count + 1
END --WHILE

CLOSE tables_cursor

SELECT trigger_code AS [--generated_create_trigger_code]
FROM #sql
ORDER BY
    table_name, id

SELECT '--Total triggers generated: ' + CAST(@trigger_count AS varchar(10))

SET NOCOUNT OFF

Msg 156, Level 15, State 1, Line 56
Incorrect syntax near the keyword 'EXISTS'.

At most, maybe 10 in a bigger table.

I get the error message restricting size when I do a large update on a row of data, I've had to pull out notes fields because of it, and store those notes fields in a separate table.

It's easier to read the output if you press Ctrl-T before running (you can press Ctrl-D to get back to grid mode).


SET NOCOUNT ON

DECLARE @table_name_pattern varchar(128)
IF OBJECT_ID('tempdb..#xtypes') IS NOT NULL
    DROP TABLE #xtypes
CREATE TABLE #xtypes (
    xtype tinyint PRIMARY KEY
    )
DECLARE @trigger_code_template varchar(8000)

SET @table_name_pattern = '%'
INSERT INTO #xtypes
    SELECT xtype
    FROM systypes
    WHERE
        name IN ('date', 'datetime', 'smalldatetime', 'datetime2', 'datetimeoffset') --adjust as preferred
SET @trigger_code_template = '
CREATE TRIGGER $trigger_name$
ON [$schema$].[$table$]
AFTER INSERT, UPDATE
AS
SET NOCOUNT ON;
UPDATE t
SET $columns_update$
FROM [$schema$].[$table$] t
/* you must correct this join before using the trigger */
INNER JOIN inserted i ON
    i.id = t.id
GO
'

------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID('tempdb..#sql') IS NOT NULL
    DROP TABLE #sql
CREATE TABLE #sql (
    id int IDENTITY(1, 1) NOT NULL,
    table_name varchar(50) NOT NULL,
    trigger_code varchar(8000)
    )

DECLARE @trigger_name_pattern sysname
DECLARE @schema_id int
DECLARE @table_name sysname
DECLARE @object_id int
DECLARE @columns_update varchar(8000)
DECLARE @trigger_count int

SET @trigger_name_pattern = '$table$__TRG_Adjust_Dates'

DECLARE tables_cursor CURSOR FAST_FORWARD LOCAL FOR
SELECT 0, t.name, t.id
FROM sysobjects t
WHERE
    t.name LIKE @table_name_pattern AND
    t.type = 'U' AND
    EXISTS (
        SELECT 1
        FROM syscolumns c
        WHERE
            c.id = t.id AND
            c.xtype IN (SELECT xtype FROM #xtypes)
        )

OPEN tables_cursor

SET @trigger_count = 0
WHILE 1 = 1
BEGIN
    FETCH NEXT FROM tables_cursor INTO @schema_id, @table_name, @object_id
    IF @@FETCH_STATUS = -1
        BREAK
    IF @@FETCH_STATUS = -2
        CONTINUE
    SET @columns_update = ''
    SELECT @columns_update = @columns_update +
            ',[' + CAST(c.name AS varchar(128)) + '] = ' + 'CASE WHEN ' +
            '[' + CAST(c.name AS varchar(128)) + '] = ''19000101'' THEN NULL ELSE ' +
            '[' + CAST(c.name AS varchar(128)) + '] END'
        FROM syscolumns c
        WHERE
            c.id = @object_id AND
            OBJECTPROPERTY(c.id, 'IsUserTable') = 1 AND
            c.xtype IN (
                SELECT xtype
                FROM systypes
                WHERE
                    name IN ('date', 'datetime', 'smalldatetime', 'datetime2', 'datetimeoffset') --adjust as preferred
                )
    SET @columns_update = SUBSTRING(@columns_update, 2, 8000)
    INSERT INTO #sql ( table_name, trigger_code )
    SELECT
        @table_name,
        'IF OBJECT_ID(''' + REPLACE(@trigger_name_pattern, '$table$', @table_name) + ''') IS NOT NULL' + CHAR(10) +
        '    DROP TRIGGER ' + REPLACE(@trigger_name_pattern, '$table$', @table_name) + CHAR(10) +
        'GO '    
    INSERT INTO #sql ( table_name, trigger_code )
    SELECT
        @table_name,
        LEFT(REPLACE(REPLACE(REPLACE(REPLACE(@trigger_code_template,
            '$trigger_name$', REPLACE(@trigger_name_pattern, '$table$', @table_name)),
            '$schema$', 'dbo'),
            '$table$', @table_name),
            '$columns_update$', @columns_update), 8000) AS [--trigger_code]
    SET @trigger_count = @trigger_count + 1
END --WHILE

CLOSE tables_cursor

SELECT trigger_code AS [--generated_create_trigger_code]
FROM #sql
ORDER BY
    table_name, id

SELECT '--Total triggers generated: ' + CAST(@trigger_count AS varchar(10))

SET NOCOUNT OFF

Ok, it outputted an error at the beginning, then printed all of the triggers to the screen, here is an example of one:

CREATE TRIGGER test__TRG_Adjust_Dates
ON [dbo].[test]
AFTER INSERT, UPDATE
AS
SET NOCOUNT ON;
UPDATE t
SET [CheckDate] = CASE WHEN [CheckDate] = '19000101' THEN NULL ELSE [CheckDate] END,[CheckDate2] = CASE WHEN [CheckDate2] = '19000101' THEN NULL
IF OBJECT_ID('TicketlessPrintingContainerInformation__TRG_Adjust_Dates') IS NOT NULL
    DROP TRIGGER TicketlessPrintingContainerInformation__TRG_Adjust_Dates
GO

So, from what I gather, I now copy these into a new trigger for every table, correct?

wait, something is off there, is it blending tables / triggers together?

And, what about for future date field adds?  Do I have to modify the trigger each time?

No, not blending, but a 'GO' is missing before the "IF OBJECT_ID(...".  Since additional GOs don't hurt anything, you could add this code:

INSERT INTO #sql ( table_name, trigger_code )
    SELECT @table_name, 'GO'
immediately before the @trigger_count is incremented.

You would have to regenerate the trigger, yes.  It's far too much overhead to make the trigger dynamically determine the columns to update.  Triggers need to run as quickly as possible.

I can't remember in SQL 2000 if you can add schemabinding to the trigger or not.

I'm not quite figuring this out.  The results seem to be cut off a lot, many times mid word, are incomplete, etc.

I think what I want, right now, is to just do our main tables affected, 5 or 6, so that won't take long, and I can do them manually.

So, I need a trigger that updates a field to null when it is 1/1/1900, and I need it to happen on an insert, and an update, and only on the row that is affected.  My current attempt does the entire table opposed to the specific row.

CREATE TRIGGER UpdateDate
ON test
AFTER INSERT, UPDATE

AS SET NOCOUNT ON;

UPDATE Test
SET

[CheckDate] = CASE WHEN [CheckDate] = '19000101' THEN NULL ELSE [CheckDate] END,
[CheckDate2] = CASE WHEN [CheckDate2] = '19000101' THEN NULL ELSE [CheckDate2]  END

That does the entire table, I need it to do just the row edited, or just the row inserted.

You dropped off the join to the inserted table, which I had in the trigger, like this:
"
/* you must correct this join before using the trigger */
INNER JOIN inserted i ON
    i.id = t.id
"

I get an ambiguous names of CheckDAte and CheckDate2

CREATE TRIGGER UpdateDate
ON test
AFTER INSERT, UPDATE

AS SET NOCOUNT ON;

UPDATE Test
SET
[CheckDate] = CASE WHEN [CheckDate] = '19000101' THEN NULL ELSE [CheckDate] END,
[CheckDate2] = CASE WHEN [CheckDate2] = '19000101' THEN NULL ELSE [CheckDate2] END

FROM Test t INNER JOIN Inserted i on i.AutoID = t.AutoID