DEV Community

FaithInErrorsZORO
FaithInErrorsZORO

Posted on

SQL triggers for blocking

AFTER INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON;

-- Look for the modified InvoiceId in the list of invoices exceeding the limit
IF (SELECT TOP 1 InvoiceId FROM Inserted) IN (
    SELECT InvoiceId
    FROM (
        SELECT IL.InvoiceId, COUNT(*) AS count
        FROM InvoiceLine IL
        JOIN Track T ON IL.TrackId = T.TrackId
        WHERE T.GenreId = 32 --  Genre
        GROUP BY IL.InvoiceId
    ) AS Summary
    WHERE count > 3
)
BEGIN
    RAISERROR ('Market Overload! Max 3 Anime items per invoice.', 16, 1);
    ROLLBACK TRANSACTION;
END
Enter fullscreen mode Exit fullscreen mode

END;

Top comments (0)