SQL Server Management Studio - after update update case

Question

I am using SQL Server Management Studio and trying to make a trigger that would after update of "order" table set "gift_id" column's values to "NULL" where the same row "reservation_id" column's value would be "0<" or NOT NULL".

Shorter explanation - if within the row "reservation_id"=NOT NULL then set "gift_id"=NULL.

I have come up with this, but I can't get this trigger right. Can someone give me a hint?

CREATE TRIGGER add_reservation
ON  [dbo].[order]
AFTER UPDATE
AS BEGIN
    UPDATE [dbo].[order]
      CASE
         WHEN [reservation_id] > 0
         THEN SET [gift_id] = NULL
      END
END
GO

Seems that there is a syntax error:

Msg 156, Level 15, State 1, Procedure add_reservation, Line 6
Incorrect syntax near the keyword 'CASE'.

`CASE` in T-SQL is an **expression** (like `a+b`) that can only **return a single, atomic value** - it is not meant to control program flow or things like that .... — marc_s, Dec 07 '15 at 09:36

score 2 · Accepted Answer · answered Dec 07 '15 at 09:33

CREATE TRIGGER add_reservation
ON  [dbo].[order]
AFTER UPDATE
AS 
BEGIN
  SET NOCOUNT ON;

UPDATE O 
 SET O.[gift_id] = NULL 
FROM [dbo].[order] O  
INNER JOIN inserted i   ON o.Pk_Col = i.Pk_Col  --<-- Primary Key column
WHERE i.[reservation_id] > 0 
   OR i.[reservation_id] IS NOT NULL
END
GO

score 0 · Answer 2 · edited May 23 '17 at 12:23

0

Found another solution by myself from this "I want to use CASE statement to update some records in sql server 2005" post.

It does what I wanted, but updates every row, so it takes a lot of resources and time. Maybe it's simpler, but not the best solution.

CREATE TRIGGER [dbo].[add_reservation]
ON  [dbo].[order]
AFTER UPDATE
AS BEGIN
UPDATE [dbo].[order]
SET [gift_id] =
( CASE 
WHEN ([reservation_id] IS NOT NULL) THEN NULL
ELSE [gift_id]
END )
END

For someone who is trying to do same at some point I came across some issues with

AT LEAST ONE OF THE RESULT EXPRESSIONS IN A CASE SPECIFICATION MUST BE AN EXPRESSION OTHER THAN THE NULL CONSTANT

To deal with that just add

 ELSE [gift_id]

edited May 23 '17 at 12:23

Community

1
1

answered Dec 07 '15 at 13:06

John

13
6

"Work like a charm" - but also updates *every* row in the table during *every* update. I'd seriously recommend you use [M.Ali's answer](http://stackoverflow.com/a/34130324/15498) or at least examine it and try to understand the [`inserted` table](https://msdn.microsoft.com/en-us/library/ms191300.aspx) – Damien_The_Unbeliever Dec 07 '15 at 13:15
Indeed, thanks! [M.Ali solution](http://stackoverflow.com/questions/34130245/sql-server-management-studio-after-update-update-case/34130324#34130324) works much better! – John Dec 07 '15 at 13:29

SQL Server Management Studio - after update update case

2 Answers2