September 15, 2010 at 6:43 am
I 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 UpdateDates
ON Foo_bar_Trig
AFTER UPDATE
AS
BEGIN
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
END
Columns to do this against:
List: Address1,Address2,Address3,City,State_Code,Province,Country,Zip,chapter_designation,EmailAddress,Phone,Mobile
I 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,
CB
September 15, 2010 at 7:20 am
CB the UPDATE function is really 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 referenced 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:
CREATE TRIGGER UpdateDates
ON Foo_bar_Trig
AFTER UPDATE
AS
BEGIN
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
Lowell
September 15, 2010 at 8:43 am
What 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.
SET NOCOUNT ON;
if OBJECT_ID('dbo.Test') IS NOT NULL DROP TABLE dbo.Test;
GO
CREATE 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);
GO
CREATE TRIGGER TestDates
ON dbo.Test
AFTER UPDATE
AS
BEGIN
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;
GO
INSERT INTO dbo.Test
SELECT 1, 'TEST ADDRESS 1', NULL, NULL, NULL, NULL, NULL UNION ALL
SELECT 2, 'Test Address 2', 'Test2', NULL, NULL, NULL, NULL UNION ALL
SELECT 3, 'Test Address 3', 'Test3', 'Test3', NULL, NULL, NULL;
-- show updating case doesn't put dates into the table
SELECT * 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 value
UPDATE dbo.Test
SET Address3 = 'MyTest'
WHERE Address3 IS NULL;
SELECT * FROM dbo.Test;
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 15, 2010 at 9:19 am
Lowell 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 : ).
CB
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply