Trigger and Updating Issue - 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 2000 Forums

Transact-SQL (2000)

Trigger and Updating Issue

Author

Topic

dewacorp.alliances

452 Posts

Posted - 2005-02-05 : 02:51:57

I had a table called Project and has a trigger called UpdateLastUpdatedProject and it's basically updating the LastUpdated field when updating any field.

CREATE TRIGGER [UpdateLastUpdatedProject] ON [dbo].[Project]
FOR UPDATE
AS
UPDATE Project
SET LastUpdated = getDate()
WHERE ProjectID = (SELECT ProjectID FROM Inserted)

But ... I just find out if I run a query like this:

UPDATE Project SET IsPublished = 1, IsDeleted = 0 WHERE ProjectID IN (7,8)

I got the following error:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

Any ideas why? If turns off the trigger that query is working fine.

So ... is the updating field for LastUpdate correct?

Thanks, VV

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-02-05 : 07:35:03

Your update affected multiple records so your subquery returned mulitple ProjectIDs. Triggers fire one time per statement. In other words, when you insert 10 records as the result of a select statement on a table with a trigger for insert, the trigger code will execute 1 time but the inserted table will contain 10 records. You just need to join to the inserted table instead of the sub-query idea:


UPDATE p
	SET p.LastUpdated = getDate()
From	Project p
JOIN	inserted i 
		ON p.ProjectID = i.ProjectID

Be One with the Optimizer
TG

dewacorp.alliances

452 Posts

Posted - 2005-02-06 : 05:34:31

Thank you so much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources