0
0
Read Time:1 Minute, 31 Second
Scenario:
Suppose you have an online store database with two tables: Orders and Products. You want to automatically update the product inventory when a new order is placed to ensure that the available quantity reflects the purchased items.
Database Tables:
OrdersTable:OrderID(Primary Key)ProductID(Foreign Key)Quantity
ProductsTable:ProductID(Primary Key)ProductNameInventoryQuantity
SQL Trigger: We’ll create an AFTER INSERT trigger on the Orders table to update the Products table’s InventoryQuantity when a new order is placed.
DELIMITER //
CREATE TRIGGER update_inventory_after_order_insert
AFTER INSERT ON Orders
FOR EACH ROW
BEGIN
DECLARE ordered_quantity INT;
SELECT NEW.Quantity INTO ordered_quantity;
-- Update product inventory
UPDATE Products
SET InventoryQuantity = InventoryQuantity - ordered_quantity
WHERE ProductID = NEW.ProductID;
END;
//
DELIMITER ;
Explanation:
- The trigger is named
update_inventory_after_order_insertand is set to execute AFTER an INSERT operation on theOrderstable. FOR EACH ROWindicates that the trigger will execute once for each row affected by the INSERT.- Inside the trigger, we declare a variable
ordered_quantityto store the quantity ordered in the new order. - We use a SELECT statement to retrieve the ordered quantity from the newly inserted row and store it in
ordered_quantity. - Then, we update the
Productstable to decrement theInventoryQuantityof the corresponding product by the ordered quantity.
Usage: Now, whenever a new order is inserted into the Orders table, the trigger will automatically update the product’s inventory in the Products table. For example:
-- Insert a new order
INSERT INTO Orders (ProductID, Quantity) VALUES (1, 5);
After executing this INSERT statement, the trigger will reduce the inventory of the product with ProductID 1 by 5 units in the Products table.
This scenario demonstrates how an SQL trigger can automate actions and help maintain data consistency in a real-world development context.
Happy
0 %
Sad
0 %
Excited
0 %
Sleepy
0 %
Angry
0 %
Surprise
0 %
About Author
There may be noticeably a bundle to learn about this. I assume you made certain good points in features also.
Hi my family member! I want to say that this post is awesome, nice written and come with approximately all significant infos. I would like to peer extra posts like this.
viagra online prescription uk
Simply desire to say your article is as surprising The clearness in your post is simply excellent and i could assume you are an expert on this subject Fine with your permission let me to grab your feed to keep up to date with forthcoming post Thanks a million and please carry on the gratifying work
Hi i think that i saw you visited my web site thus i came to Return the favore I am attempting to find things to improve my web siteI suppose its ok to use some of your ideas
Thank you for the auspicious writeup It in fact was a amusement account it Look advanced to far added agreeable from you However how can we communicate
Fantastic site Lots of helpful information here I am sending it to some friends ans additionally sharing in delicious And of course thanks for your effort
Somebody essentially lend a hand to make significantly articles Id state That is the very first time I frequented your website page and up to now I surprised with the research you made to make this actual submit amazing Wonderful task
Somebody essentially lend a hand to make significantly posts I might state That is the very first time I frequented your web page and up to now I surprised with the research you made to create this particular put up amazing Excellent job
obviously like your website but you need to test the spelling on quite a few of your posts Several of them are rife with spelling problems and I to find it very troublesome to inform the reality on the other hand Ill certainly come back again