Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: SQL Server
Expertise: Beginner
Jun 28, 2000

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 int
Subtotal money
HandlingCharge money
This trigger handles updating the HandlingCharge column with either a 5 or a 0:
CREATE TRIGGER TestOrders_INS ON [TestOrders] 
FOR INSERT
AS

IF (SELECT Subtotal FROM inserted) < 30
  UPDATE TestOrders
  SET HandlingCharge = 5
  FROM TestOrders, inserted
  WHERE TestOrders.ID = inserted.ID
ELSE
  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.
DevX Pro
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date