SQL Triggers variation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

SQL Triggers variation

Author

Topic

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2014-05-12 : 11:55:27

Variation of http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=194108
Thank you TG for the information.

What I want is a record for only the changed column.
The following gives me one record for each column in table #Columns (as requested in original post).

USE Temp_DJJ;
GO
ALTER TRIGGER trig_MyTable_Update ON MyTable
AFTER UPDATE 
AS 
BEGIN
    SET NOCOUNT ON;

    IF OBJECT_ID('tempdb..#Columns') IS NOT NULL DROP TABLE #Columns; 
    CREATE TABLE #Columns (SubjectColumn VARCHAR(50));
    INSERT INTO #Columns VALUES       -- I have many more columns (Not a table I created)
	('[MyColumn1]'),('[MyColumn2]'),('[MyColumn3]'),('[MyColumn4]'),('[MyColumn5]'),('[MyColumn6]');

    INSERT INTO Temp_DJJ.dbo.P2_TriggerLog (
        CreateDate, UserId, ServerName, ProcessName, Workstation, DatabaseName, TableName,
        FieldName, OldVal, NewVal, Key1Name, Key1Val)
    SELECT 
        GETDATE(), SYSTEM_USER, @@ServerName, APP_NAME(), HOST_NAME(), 'MyDatabase', 'MyTable' 
        , cols.subjectColumn
        , CASE 
            WHEN cols.SubjectColumn = '[MyColumn6]' THEN d.[MyColumn6]
          END
        , CASE 
            WHEN cols.SubjectColumn = '[MyColumn6]' THEN i.[MyColumn6]
          END 
        , 'RefNum', D.RefNum
    FROM inserted          i
    INNER JOIN deleted     d ON i.RefNum = d.RefNum
    INNER JOIN #Columns cols ON 
        COALESCE(i.[MyColumn6], 'NULL') <> COALESCE(d.[MyColumn6], 'NULL')
    WHERE
        COALESCE(i.[MyColumn6], 'NULL') <> COALESCE(d.[MyColumn6], 'NULL');
END
GO

The WHERE clause is my try at getting only one record created (not six). I should know how to do this but ...

Thanks

djj

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2014-05-12 : 12:10:53

I am now working with

    INNER JOIN #Columns cols ON 
        cols.SubjectColumn = 
        CASE WHEN COALESCE(i.[Status Code1], 'NULL') <> COALESCE(d.[Status Code1], 'NULL') THEN '[status code1]' else 'null' END

However, this may be going down the wrong road with multiple columns.

djj

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-05-12 : 12:58:17

I suggest sticking with CDC if available. You can't come close to the effectiveness and efficiency of pre-written code.

You can add a trigger to add the SUSER_NAME() or ORIGINAL_LOGIN() of who made the change to the row. Designate it as the first trigger to fire, just in case.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-05-12 : 13:35:19

djj,
My intention on the referenced thread was that only a row where a column changed would be inserted. That is not working?

your code above does need to be OR'd with the other columns. Did you try this? (you need to use your COALESCE with any nullable columns as you have it above)
ie:


          on  (cols.subjectColumn = 'status' and i.[status] != d.[status])
          or (cols.subjectColumn = 'amount' and i.amount != d.amount)
          or (cols.subjectColumn = 'resource' and i.[resource] != d.[resource])
          or (cols.subjectColumn = 'balance' and i.balance != d.balance)
          or (cols.subjectColumn = 'owner' and i.[owner] != d.[owner])
          or (cols.subjectColumn = 'rsn' and i.rsn != d.rsn)

Be One with the Optimizer
TG

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2014-05-12 : 15:25:32

TG, Your code in the thread for ravenhawk08 works as was requested. Since I did not want exactly the same thing I started a new thread.
I got busy and did not get back to say I found the answer which was to use a case statement and the or

inner join #Columns cols on 
cols.SubjectColumn = CASE WHEN COALESCE(i.[MyColumn1], -9999) <> COALESCE(d.[MyColumn1], -9999) THEN '[MyColumn1]' ELSE 'NULL' END
OR cols.SubjectColumn = CASE WHEN COALESCE(i.[MyColumn2], 'NULL') <> COALESCE(d.[MyColumn2], 'NULL') THEN '[MyColumn2]' ELSE 'NULL' END
OR cols.SubjectColumn = CASE WHEN COALESCE(i.[MyColumn3], 'NULL') <> COALESCE(d.[MyColumn3], 'NULL') THEN '[MyColumn3]' ELSE 'NULL' END

djj

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources