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.
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 UpdateDateON test AFTER INSERT, UPDATEASUPDATE Test SET [CheckDate] = CASE WHEN [CheckDate] = '19000101' THEN NULL ELSE [CheckDate] END, [CheckDate2] = CASE WHEN [CheckDate2] = '19000101' THEN NULL ELSE [CheckDate2] ENDwhere 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 7Subquery 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 terminatedHow 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] ENDfrom inserted iinner join test t on t.autoID = i.autoID Be One with the OptimizerTG |
|
|
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] ENDfrom inserted iinner join test t on t.autoID = i.autoID Be One with the OptimizerTG
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] ENDfrom inserted iinner join test t on t.autoID = i.autoID Be One with the OptimizerTG
that did the trick, thanks |
|
|
|
|
|
|
|