Create Trigger With If Statement

Question

My problem is this:

Create a trigger named Products_UPDATE that checks the new value for the DiscountPercent column of the Products table. This trigger should raise an appropriate error if the discount percent is greater than 100 or less than 0.

If the new discount percent is between 0 and 1, this trigger should modify the new discount percent by multiplying it by 100. That way, a discount percent of .2 becomes 20.

Test this trigger with an appropriate UPDATE statement.

CREATE TRIGGER Products_UPDATE
ON Products
AFTER INSERT, UPDATE
AS
BEGIN 
IF (SELECT Products.DiscountPercent FROM Products 
JOIN inserted ON Products.DiscountPercent = inserted.DiscountPercent) > 100 OR (SELECT Products.DiscountPercent FROM Products 
JOIN inserted ON Products.DiscountPercent = inserted.DiscountPercent) < 0
RAISERROR(N'Percent has to be between 0 and 100',16,1)
END
BEGIN
IF
(SELECT Products.DiscountPercent FROM Products 
JOIN inserted ON Products.DiscountPercent = inserted.DiscountPercent) > 0 AND (SELECT Products.DiscountPercent FROM Products 
JOIN inserted ON Products.DiscountPercent = inserted.DiscountPercent) < 1
UPDATE Products
SET DiscountPercent = DiscountPercent * 100
WHERE DiscountPercent = (SELECT Products.DiscountPercent FROM Products 
JOIN inserted ON Products.DiscountPercent = inserted.DiscountPercent)
END
GO
UPDATE Products
SET DiscountPercent = 103
WHERE ProductID = 1;

So it isn't throwing an error when I update the DiscountPercent greater than 100.

Dale K · Accepted Answer · 2020-08-03 02:21:40Z

1

I'm not quite sure what your joins were trying to accomplish. You should be able to carry out your check and insert entirely using the Inserted pseudo-table. You don't need a second check for the update as the raiserror will not allow the rest of the trigger to continue.

You can use a case expression to compute the discount.

CREATE TRIGGER Products_UPDATE
ON Products
AFTER INSERT, UPDATE
AS
BEGIN 
  SET NOCOUNT ON;

  IF EXISTS (
    SELECT 1
    FROM Inserted I
    WHERE I.DiscountPercent > 100 OR I.DiscountPercent < 0
    ) BEGIN
    -- Should be using throw
    RAISERROR(N'Percent has to be between 0 and 100',16,1);
  END;

  UPDATE Products SET
    DiscountPercent = DiscountPercent * 100
  WHERE id IN (SELECT id FROM Inserted)
  AND DiscountPercent < 1;
END;
GO

Note: You should really be using throw rather than raiserror now.

edited Aug 3, 2020 at 2:21

answered Aug 3, 2020 at 1:29

Dale K

28.1k15 gold badges59 silver badges85 bronze badges

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

1 Comment

SMor Over a year ago

Far better to move the check on DiscountPercent to the where clause in the update statement to avoid pointlessly multiplying the column by 1 (which will again cause the update trigger to run)

Collectives™ on Stack Overflow

Create Trigger With If Statement

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related