Error on trigger when doing a multiple row 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

General SQL Server Forums

New to SQL Server Programming

Error on trigger when doing a multiple row update

Author

Topic

dzirkelb
Yak Posting Veteran

53 Posts

Posted - 2014-04-15 : 13:42:19

I have the following trigger that updates a couple test fields to null when they are 1/1/1900, works great on inserts, and one line updates:

CREATE TRIGGER UpdateDate
ON test
AFTER INSERT, UPDATE
AS
UPDATE Test
SET
[CheckDate] = CASE WHEN [CheckDate] = '19000101' THEN NULL ELSE [CheckDate] END,
[CheckDate2] = CASE WHEN [CheckDate2] = '19000101' THEN NULL ELSE [CheckDate2] END
where AutoID = (select AutoID from inserted)

However, when trying to do a multi line update statement, I get the following error:

Msg 512, Level 16, State 1, Procedure UpdateDate, Line 7
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

How can I get around this?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-04-15 : 13:57:19

The obvious advice is that you need to be able to handle multiple rows in the Inserted table. In your example above it will return more than one row, hence the error you are receiving. Can you join from Test to Inserted? If so, I'd do that.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-04-15 : 13:59:54

Can you post your "multi line update statement"?

Also, you might want to do a join on the inserted records to handle multiple rows or add a TOP (1) to the select statement at the end.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-04-15 : 14:03:45

Here's what they're talking about:


update t set
       t.[CheckDate] = CASE WHEN i.[CheckDate] = '19000101' THEN NULL ELSE i.[CheckDate] END
       ,t.[CheckDate2] = CASE WHEN i.[CheckDate2] = '19000101' THEN NULL ELSE i.[CheckDate2] END
from   inserted i
inner join test t on t.autoID = i.autoID

Be One with the Optimizer
TG

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-04-15 : 14:15:26

quote:

Originally posted by TG

Here's what they're talking about:


update t set
       t.[CheckDate] = CASE WHEN i.[CheckDate] = '19000101' THEN NULL ELSE i.[CheckDate] END
       ,t.[CheckDate2] = CASE WHEN i.[CheckDate2] = '19000101' THEN NULL ELSE i.[CheckDate2] END
from   inserted i
inner join test t on t.autoID = i.autoID

Be One with the Optimizer
TG

OK -- so change your trigger to handle more than one row. That means changing your original WHERE clause to a JOIN with the inserted rows on the ID column, I suspect.

dzirkelb
Yak Posting Veteran

53 Posts

Posted - 2014-04-15 : 16:51:50

quote:

Originally posted by TG

Here's what they're talking about:


update t set
       t.[CheckDate] = CASE WHEN i.[CheckDate] = '19000101' THEN NULL ELSE i.[CheckDate] END
       ,t.[CheckDate2] = CASE WHEN i.[CheckDate2] = '19000101' THEN NULL ELSE i.[CheckDate2] END
from   inserted i
inner join test t on t.autoID = i.autoID

Be One with the Optimizer
TG

that did the trick, thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources