CODEWITHSUNDEEP
sqlsql-server
modulitos
modulitos

Reputation: 15804

SQL: Setting trigger to limit inserts

This example is a video rental store with entities Customer, Plan, and Rental. Each customer has a plan, and each plan has a maximum number of rentals. I am trying to enforce the constraint on the maximum number of video rentals. I am using SQL Server 2012.

Here is my attempt at creating a trigger:

CREATE TRIGGER maxMovies
ON Rental 
FOR INSERT
AS 
BEGIN
  IF (0 > (SELECT count(*) 
           FROM (SELECT count(*) as total 
                 FROM Inserted i, rental r 
                 WHERE i.customerID = r.customerID) as t, Inserted i, Rental r 
           WHERE t.total > r.max_movies AND i.customerID = r.customerID) )
  BEGIN
    RAISEERROR("maximum rentals surpassed.")
    ROLLBACK TRAN    
  END
END

-- (rest of query)
DROP table...

However, SQL Server gives me the following errors:

Msg 102, Level 15, State 1, Procedure maxMovies, Line 10
Incorrect syntax near 'RAISEERROR'.
Msg 156, Level 15, State 1, Procedure maxMovies, Line 15
Incorrect syntax near the keyword 'DROP'.

Any suggestions on how to create this trigger?

Upvotes: 0

Views: 118

Answers (3)

Bogdan Sahlean
Bogdan Sahlean

Reputation: 1

CREATE TRIGGER trgI_Rental_VerifyMaxMovies 
ON  dbo.Rental 
FOR INSERT /*, UPDATE*/ -- -- You should also check this limit in the case of UPDATE
AS 
BEGIN
    IF /*UPDATE(CustomerID) AND*/ EXISTS ( -- Uncomment in the case of FOR , UPDATE
        SELECT  * 
        FROM    dbo.[Plan] p INNER JOIN dbo.Rental r ON r.CustomerID = p.CustomerID -- Assumption: "Each customer has a plan"
        WHERE   EXISTS(SELECT * FROM inserted i WHERE i.CustomerID = p.CustomerId)
        GROUP BY p.CustomerID, p.MaxMovies
        HAVING  p.MaxMovies > COUNT(*)
    )
    BEGIN
        ROLLBACK TRAN;
        RAISERROR('maximum rentals surpassed.', 16, 1);    
    END
END

Upvotes: 0

Trinimon
Trinimon

Reputation: 13967

Use single quotes instead of quotation marks ...

RAISERROR('maximum rentals surpassed.');

remove one E and add a colon ;.

Upvotes: 1

Thorsten Dittmar
Thorsten Dittmar

Reputation: 56707

It is RAISERROR not RAISEERROR - a simple typo. And of course, as Trinimon spotted correctly, strings need to be quoted in single quotes, not double quotes.

RAISERROR('maximum rentals surpassed.')

Upvotes: 1

Related Questions