Create a trigger named trg_line_prod that automatically updates the quantity on hand for each product sold after a new LINE row is added.
Hello, I have a few questions in regards to MYSQL. It was answered on the website before, but whenever I put the answer in my IDE I get a syntax error, so I will ask here instead for the few I need help with. The topic is MySQL triggers.
The second question (or task) is as follows:
"Create a trigger named trg_line_prod that automatically updates the quantity on hand for each product sold after a new LINE row is added."
An in-depth explanation is appreciated, thank you for your time.
The table attached contains the
Trigger:
The trigger is a named set of SQL statement that is executed at the time of data modification or meets some data description.
- If the stated trigger condition meets up, then the mentioned action in the trigger has been performed. There are three parts of trigger; they are:
Event:
- It specifies the change to be made, such as an UPDATE or DELETE of a record. Condition:
- It specifies the condition to examine the records about to be affected. For each record, that meets the condition given in the trigger.
Action:
- The action performed after the start of the trigger.
Query to create trigger:
CREATE OR REPLACE TRIGGER TRG_LINE_PROD
AFTER INSERT ON LINE
FOR EACH ROW
BEGIN
UPDATE PRODUCT SETP_QOH = P_QOH - :NEW.LINE UNITS
WHERE PRODUCT.P_CODE = :NEW._CODE:
END;
/
Step by step
Solved in 4 steps with 4 images
