Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Update Trigger Loop? Expand / Collapse
Author
Message
Posted Wednesday, September 15, 2010 6:43 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 1:49 PM
Points: 10, Visits: 64
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
Post #986213
Posted Wednesday, September 15, 2010 7:20 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:24 PM
Points: 12,881, Visits: 31,816
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #986245
Posted Wednesday, September 15, 2010 8:43 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:55 PM
Points: 6,582, Visits: 8,860
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
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #986329
Posted Wednesday, September 15, 2010 9:18 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 1:49 PM
Points: 10, Visits: 64
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
Post #986383
Posted Wednesday, September 15, 2010 10:26 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, July 20, 2014 11:55 AM
Points: 1,945, Visits: 2,860
Audit 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.

Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #986466
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse