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.
