devxlogo

If/Then Statement

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.

See also  5 Strategies for Supercharging Your CRM
devxblackblue

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist