InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 03:57:45 GMT20http://www.sqlservercentral.com/Forums/Topic986213-1292-1.aspxAudit data is best collected with a third party tool that uses the log files and other tricks. They little overhead, better tools than you want to write and don't cost that much. They also stand up in court and get approved by auditors and lawyers.Wed, 15 Sep 2010 10:26:09 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic986213-1292-1.aspxLowell and Wayne - thank you for the leads. @Lowell, yes those fields do exist (apologies for not providing a create table script) and your code is a great starting point. @Wayne, Thank you for providing a demonstrable example of null behavior - I'll be sure to experiment with this to learn and test these scenarios against what ever trigger I develop.If I need more help, at least I'll have some closer to real code to post : ).CBWed, 15 Sep 2010 09:18:59 GMTclb39http://www.sqlservercentral.com/Forums/Topic986213-1292-1.aspxWhat happens if the column is, and remains, NULL? Or if it's being updated from NULL to a value? Many addresses won't have an address2/3, and that could be null in both the inserted and deleted tables. Or it could be changed from NULL to a value due to running the address through an address verification routine for getting cheaper mailing rates (CASS).Another potential issue: do you want the date updated if the case of the field is changed, and that's the only change? To handle this, you'll need to deal with case sensitive collations.Here's an example that shows the NULL issue. Just use the remarked out lines in the trigger to handle them being updated.[code="sql"]SET NOCOUNT ON;if OBJECT_ID('dbo.Test') IS NOT NULL DROP TABLE dbo.Test;GOCREATE TABLE dbo.Test ( RowID int PRIMARY KEY CLUSTERED, Address1 varchar(50) NULL, Address2 varchar(50) NULL, Address3 varchar(50) NULL, Address1Date datetime NULL, Address2Date datetime NULL, Address3Date datetime NULL);GOCREATE TRIGGER TestDatesON dbo.TestAFTER UPDATEASBEGIN SET NOCOUNT ON; UPDATE dbo.Test SET Address1Date = GetDate() FROM dbo.Test JOIN inserted i ON i.RowID = Test.RowID JOIN deleted d ON d.RowID = Test.RowID WHERE i.Address1 <> d.Address1-- OR (d.Address1 IS NULL AND i.Address1 IS NOT NULL); UPDATE dbo.Test SET Address2Date = GetDate() FROM dbo.Test JOIN inserted i ON i.RowID = Test.RowID JOIN deleted d ON d.RowID = Test.RowID WHERE i.Address2 <> d.Address2-- OR (d.Address2 IS NULL AND i.Address2 IS NOT NULL); UPDATE dbo.Test SET Address3Date = GetDate() FROM dbo.Test JOIN inserted i ON i.RowID = Test.RowID JOIN deleted d ON d.RowID = Test.RowID WHERE i.Address3 <> d.Address3-- OR (d.Address3 IS NULL AND i.Address3 IS NOT NULL);END;GOINSERT INTO dbo.TestSELECT 1, 'TEST ADDRESS 1', NULL, NULL, NULL, NULL, NULL UNION ALLSELECT 2, 'Test Address 2', 'Test2', NULL, NULL, NULL, NULL UNION ALLSELECT 3, 'Test Address 3', 'Test3', 'Test3', NULL, NULL, NULL;-- show updating case doesn't put dates into the tableSELECT * FROM dbo.Test;UPDATE dbo.Test SET Address1 = UPPER(Address1), Address2 = UPPER(Address2), Address3 = UPPER(Address3);SELECT * FROM dbo.Test;-- show effect of updating a field from NULL to a valueUPDATE dbo.Test SET Address3 = 'MyTest' WHERE Address3 IS NULL;SELECT * FROM dbo.Test;[/code]Wed, 15 Sep 2010 08:43:58 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic986213-1292-1.aspxCB the UPDATE function is [b]really[/b] misleading; it doesn't tell you if something changed or not(which is what you intuitively assume based on the name)it returns true or false if the column was [b]referenced [/b]in the insert update statement, NOT if the data changed; to check if things changed, you need to compare the columns by joining the INSERTED and DELETED tables instead;something else taht might not be obvious, is that your trigger can do multiple things in it;here's an example i fleshed out, but i do not know if some of the columns exist, like "ChangeDate_City" that my code assumes:[code]CREATE TRIGGER UpdateDates ON Foo_bar_TrigAFTER UPDATEASBEGIN SET NOCOUNT ON; UPDATE Foo_bar_Trig SET ChangeDate_Address1 = getdate() FROM INSERTED i INNER JOIN DELETED d ON i.Address_id = d.Address_id INNER JOIN Foo_bar_Trig a ON i.Address_id = a.Address_id --did the address change? WHERE i.Address1 <> d.Address1 UPDATE Foo_bar_Trig SET ChangeDate_Address2 = getdate() FROM INSERTED i INNER JOIN DELETED d ON i.Address_id = d.Address_id INNER JOIN Foo_bar_Trig a ON i.Address_id = a.Address_id --did the address2 change? WHERE i.Address2 <> d.Address2 UPDATE Foo_bar_Trig SET ChangeDate_Address3 = getdate() FROM INSERTED i INNER JOIN DELETED d ON i.Address_id = d.Address_id INNER JOIN Foo_bar_Trig a ON i.Address_id = a.Address_id --did the address3 change? WHERE i.Address3 <> d.Address3 UPDATE Foo_bar_Trig SET ChangeDate_City = getdate() FROM INSERTED i INNER JOIN DELETED d ON i.Address_id = d.Address_id INNER JOIN Foo_bar_Trig a ON i.Address_id = a.Address_id --did the address3 change? WHERE i.City <> d.City --City,State_Code,Province,Country,Zip,chapter_designation,EmailAddress,Phone,Mobile END[/code]Wed, 15 Sep 2010 07:20:56 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic986213-1292-1.aspxI have a simple Trigger (below) that updates a date value if a column value has changed. I need to do this same action over multiple fields and I know I can create a separate trigger for each column, but I'm hoping there is a more elegant solution...Simple Trigger:CREATE TRIGGER UpdateDatesON Foo_bar_TrigAFTER UPDATEASBEGIN SET NOCOUNT ON; IF UPDATE (Address1) BEGIN Update Foo_bar_Trig Set ChangeDate_Address1 = getdate () From inserted i inner join Foo_bar_Trig a on i.Address_id = a.Address_id ENDColumns to do this against:List: Address1,Address2,Address3,City,State_Code,Province,Country,Zip,chapter_designation,EmailAddress,Phone,MobileI puttered with creating a variable for each column name then looping over the list, but so far I haven't gotten the syntax right (I'm not going to bother listing cr@p code).Hopefully someone can a) confirm the only way is separate triggers, b) point me to URLS/books/information to learn these skills c) spoon feed me correct code.Thanks in Advance,CBWed, 15 Sep 2010 06:43:12 GMTclb39