SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Error on trigger when doing a multiple row update
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dzirkelb
Yak Posting Veteran

53 Posts

Posted - 04/15/2014 :  13:42:19  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 04/15/2014 :  13:57:19  Show Profile  Reply with Quote
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.
Go to Top of Page

gbritton
Flowing Fount of Yak Knowledge

1752 Posts

Posted - 04/15/2014 :  13:59:54  Show Profile  Reply with Quote
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.
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 04/15/2014 :  14:03:45  Show Profile  Reply with Quote
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

Edited by - TG on 04/15/2014 14:04:45
Go to Top of Page

gbritton
Flowing Fount of Yak Knowledge

1752 Posts

Posted - 04/15/2014 :  14:15:26  Show Profile  Reply with Quote
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.
Go to Top of Page

dzirkelb
Yak Posting Veteran

53 Posts

Posted - 04/15/2014 :  16:51:50  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000