• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 308
  • Last Modified:

Need help with a sql trigger

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
dzirkelb
Asked:
dzirkelb
  • 13
  • 13
1 Solution
 
Lee SavidgeCommented:
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
 
dzirkelbAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
dzirkelbAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
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
 
dzirkelbAuthor Commented:
That would be awesome, and beyond the scope of my brain :)
0
 
Scott PletcherSenior DBACommented:
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
 
dzirkelbAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
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
 
dzirkelbAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
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
 
Scott PletcherSenior DBACommented:
Please try to copy and paste the code from my immediately previous post.  Let me know if it doesn't run.
0
 
dzirkelbAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
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
 
dzirkelbAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
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
 
dzirkelbAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
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
 
dzirkelbAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
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
 
dzirkelbAuthor Commented:
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
 
dzirkelbAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
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
 
dzirkelbAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
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
 
dzirkelbAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 13
  • 13
Tackle projects and never again get stuck behind a technical roadblock.
Join Now