UPDATE Trigger issue when using UPDATE

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 2005 Forums

Transact-SQL (2005)

UPDATE Trigger issue when using UPDATE

Author

Topic

cnaypi
Starting Member

22 Posts

Posted - 2008-05-30 : 18:29:39

I am trying to update a fields with an UPDATE statement but I keep getting the error message when I run the query.

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

I have this Update trigger that I know is causing the error message because I guess it's not built to manage multi-row updates.

Can someone help me re-write it. I also tried using the WHERE p.ID = p.ID but when I do that it modifies all rows in the modifieddate column instead of just the cells/rows that I'm updating

ALTER TRIGGER [dbo].[MultitrigCA]
ON [dbo].[ProdDesc]
AFTER UPDATE
AS

SET NOCOUNT ON

IF UPDATE (codeabbreviation)
UPDATE p
sET p.ModifiedDate = GETDATE()
FROM ProdDesc AS p
WHERE p.ID = (SELECT ID FROM inserted)

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-05-30 : 18:49:22

[code]... rest of trigger ...
update p
set
p.ModifiedDate = GETDATE()
from
ProdDesc p
where
p.ID in (select ID from inserted)
[/code]

CODO ERGO SUM

cnaypi
Starting Member

22 Posts

Posted - 2008-05-30 : 19:37:43

Thanks Michael. I'm no longer getting the error but it seems that it's still updating all the rows and I think the problem is with the update statement that I'm using.

When I run this query I get the result message (23265 row(s) affected) even though it only updated 10rows and this is the reason why the trigger is updating all 23265 rows.

Update ProdDesc
Set codeabbreviation = replace(codeabbreviation, 'Adapter','Adptr')

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-31 : 00:56:31

quote:
Originally posted by cnaypi

Thanks Michael. I'm no longer getting the error but it seems that it's still updating all the rows and I think the problem is with the update statement that I'm using.

When I run this query I get the result message (23265 row(s) affected) even though it only updated 10rows and this is the reason why the trigger is updating all 23265 rows.

Update ProdDesc
Set codeabbreviation = replace(codeabbreviation, 'Adapter','Adptr')

Can you post your table structure? Also is ID the unique value column of your table?

cnaypi
Starting Member

22 Posts

Posted - 2008-05-31 : 03:02:19

Here is my table structure and yes ID is the unique value column in my table. Thanks
ID int
CodeId nvarchar(50)
CodeCategoryId nvarchar(50)
CodeCategory nvarchar(50)
CodeCategoryName nvarchar(50)
Code nvarchar(50)
CodeName nvarchar(250)
CodeAbbreviation nvarchar(250)
FullTextName nvarchar(250)
Suggestedabbreviation nvarchar(72)
ModifiedDate datetime

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-31 : 12:32:50

May be this:-

ALTER TRIGGER [dbo].[MultitrigCA]
ON [dbo].[ProdDesc]
AFTER UPDATE
AS

SET NOCOUNT ON

IF UPDATE (codeabbreviation)
UPDATE p
SET p.ModifiedDate = GETDATE()
FROM ProdDesc AS p
INNER JOIN INSERTED i
ON i.ID=p.ID
GO

If this is not one you desire can you explain your requirement with some data sample.

cnaypi
Starting Member

22 Posts

Posted - 2008-06-02 : 13:19:19

Hello Visakh The issue that I have is with my update statement. I will open a new topic on it since this one is for the UPDATE Trigger and my UPDATE TRIGGER is now working Thanks to you and Michael.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-02 : 13:33:03

quote:
Originally posted by cnaypi

Hello Visakh The issue that I have is with my update statement. I will open a new topic on it since this one is for the UPDATE Trigger and my UPDATE TRIGGER is now working Thanks to you and Michael.

You're welcome. I've provided answer there

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources