If/Then Statement

Question:
I am building an e-commerce site with SQL as the database. When an order is placed, how do I have the database set the HandlingCharge column to 5 if the SubTotal is less than 30 and 0 if it’s greater than 30?

Answer:
To update the HandlingCharge column, create an insert trigger that checks the value of the Subtotal column. I created this test table called TestOrders:

ID intSubtotal moneyHandlingCharge money

This trigger handles updating the HandlingCharge column with either a 5 or a 0:

CREATE TRIGGER TestOrders_INS ON [TestOrders] FOR INSERTASIF (SELECT Subtotal FROM inserted) < 30  UPDATE TestOrders  SET HandlingCharge = 5  FROM TestOrders, inserted  WHERE TestOrders.ID = inserted.IDELSE  UPDATE TestOrders  SET HandlingCharge = 0  FROM TestOrders, inserted  WHERE TestOrders.ID = inserted.ID

The inserted table is a special table available only in a trigger that contains the rows that are being inserted or updated.

Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

Recent Articles: