SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 SQL Triggers variation
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

djj55
Constraint Violating Yak Guru

USA
318 Posts

Posted - 05/12/2014 :  11:55:27  Show Profile  Reply with Quote
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

USA
318 Posts

Posted - 05/12/2014 :  12:10:53  Show Profile  Reply with Quote
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
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
364 Posts

Posted - 05/12/2014 :  12:58:17  Show Profile  Reply with Quote
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.
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 05/12/2014 :  13:35:19  Show Profile  Reply with Quote
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
Go to Top of Page

djj55
Constraint Violating Yak Guru

USA
318 Posts

Posted - 05/12/2014 :  15:25:32  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000