Solved

Need help with a sql trigger

Posted on 2014-04-11
27
297 Views
Last Modified: 2014-04-15
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')
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

**COLUMN_NAME FROM ABOVE** would be whatever resulted from the schema query.

Anyone think this is possible or understand what I'm asking?
0
Comment
Question by:dzirkelb
  • 13
  • 13
27 Comments
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 39994333
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?
0
 

Author Comment

by:dzirkelb
ID: 39994379
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.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39994890
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)?
0
 

Author Comment

by:dzirkelb
ID: 39994899
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?
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39994942
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.
0
 

Author Comment

by:dzirkelb
ID: 39994952
That would be awesome, and beyond the scope of my brain :)
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39995018
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
0
 

Author Comment

by:dzirkelb
ID: 39995050
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?
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39995071
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.
0
 

Author Comment

by:dzirkelb
ID: 39995086
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.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39995100
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

Open in new window

0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39995114
Please try to copy and paste the code from my immediately previous post.  Let me know if it doesn't run.
0
 

Author Comment

by:dzirkelb
ID: 39995118
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 '$'.
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39995144
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
0
 

Author Comment

by:dzirkelb
ID: 39995156
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.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39995204
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
0
 

Author Comment

by:dzirkelb
ID: 39995220
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.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39995245
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
0
 

Author Comment

by:dzirkelb
ID: 39995256
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.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39995296
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
0
 

Author Comment

by:dzirkelb
ID: 39995300
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?
0
 

Author Comment

by:dzirkelb
ID: 39995307
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?
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 39995336
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.
0
 

Author Comment

by:dzirkelb
ID: 40000248
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.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40000287
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
"
0
 

Author Comment

by:dzirkelb
ID: 40000312
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
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
ID: 40000329
Ah, that's a good point.  We'll need to add an alias to the generated code:

...
    IF @@FETCH_STATUS = -2
        CONTINUE
    SELECT @columns_update = ... +
            ',[' + CAST(c.name AS varchar(128)) + '] = ' + 'CASE WHEN ' +
            't.[' + CAST(c.name AS varchar(128)) + '] = ''19000101'' THEN NULL ELSE ' +
            't.[' + CAST(c.name AS varchar(128)) + '] END'
        FROM sys.columns c
        WHERE
...
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now