Include an If statement in a SQL Server trigger?

Question

I'm currently struggling in a database management class. I have been tasked with creating a trigger that prints a message if a customer makes a reservation for the first time. My code so far is as follows

ALTER TRIGGER [dbo].[ResNewTrigger] 
ON [dbo].[Reservation] 
AFTER INSERT, DELETE, UPDATE
AS 
BEGIN
    SET NOCOUNT ON;

    DECLARE @cid CHAR(4)   
    DECLARE @res_counts INT    
    SELECT @cid = CustomerNum FROM inserted    

    SELECT @res_counts = COUNT(*) 
    FROM dbo.Reservation 
    WHERE CustomerNum = @cid 

    IF @res_counts = 0 THEN
        PRINT  'A reservation is made for the first time from customer' + @cid;
    END IF;
END;

This throws the errors:

Msg 156, Level 15, State 1, Procedure ResNewTrigger, Line 20 [Batch Start Line 7]
Incorrect syntax near the keyword 'THEN'.

Msg 102, Level 15, State 1, Procedure ResNewTrigger, Line 22 [Batch Start Line 7]
Incorrect syntax near ';'.

I must not know the proper syntax or something but I have had zero luck finding how to solve this issue.

One issue is that you are assuming there will only be 1 row in inserted - you can never do that as inserted will contain as many rows as are updated/inserted, and you have to be able to handle that. There are many SQL Server trigger tutorials around which show how. — Dale K
– Dale K, Commented Nov 20, 2019 at 1:56
If you check the docs if/begin/end doesn't start with then nor finish with end if. — Dale K
– Dale K, Commented Nov 20, 2019 at 1:58
You need to get into the habit of checking docs.Otherwise you'll need to get used to people telling you to rtfm. Everything is just a google away — Nick.Mc
– Nick.Mc, Commented Nov 20, 2019 at 2:02

Dale K · Accepted Answer · 2019-11-20 02:29:14Z

1

The syntax error in your IF statement is the least of your problems with regards to the trigger (see the docs to correct the IF statement).

Your trigger is wrong because you are assuming that inserted has one row. Such an assumption is NEVER safe.

ALTER TRIGGER [dbo].[ResNewTrigger] 
   ON  [dbo].[Reservation] 
   AFTER INSERT
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @cids VARCHAR(MAX);

    -- Insert statements for trigger here
    SELECT @cids = STRING_AGG(i.cid, ', ')
    FROM inserted i JOIN
         Reservation r
         ON r.customerNum = i.customerNum
    GROUP BY i.cid
    HAVING COUNT(*) = 1;   -- this row is already in reservation
    IF @cids <> ''
    BEGIN
        PRINT  'A reservation is made for the first time from customer(s) ' + @cids;
    END;
END;

This concatenates the cids together using string_agg(). In earlier versions of SQL Server, you need a UDF or some other mechanism to aggregate the strings.

I removed the UPDATE and DELETE portions of the trigger s well. You certainly cannot get a new reservation for a customer on a DELETE. On an UPDATE, it is more questionable. However, it is difficult (but not impossible) to differentiate between a change to a single record and inserting a single record.

edited Nov 20, 2019 at 2:29

Dale K

28.1k15 gold badges59 silver badges85 bronze badges

answered Nov 20, 2019 at 1:56

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Gordon Linoff Over a year ago

I'm quite surprised at how this could get a downvote. Is someone (anonymously) suggesting NOT taking into account that inserted could have multiple rows?

Gordon Linoff Over a year ago

@DaleK . . . If you only address the syntax error you are leaving a trigger with a MAJOR BUG in it. That would seem to be the bigger issue. The OP does not know how to write a correct trigger.

Collectives™ on Stack Overflow

Include an If statement in a SQL Server trigger?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related