CODEWITHSUNDEEP
sql-servert-sqltriggers
user13724007
user13724007

Reputation:

Create Trigger With If Statement

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.

Upvotes: 0

Views: 1232

Answers (1)

Dale K
Dale K

Reputation: 27472

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.

Upvotes: 1

Related Questions