CODEWITHSUNDEEP
sqlsql-servertriggers
user3183938
user3183938

Reputation: 5

Include an If statement in a SQL Server trigger?

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.

Upvotes: 0

Views: 951

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271231

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.

Upvotes: 1

Related Questions