Open the trigger named invoices_before_update, provided below. DELIMITER // CREA
ID: 3833770 • Letter: O
Question
Open the trigger named invoices_before_update, provided below.
DELIMITER //
CREATE TRIGGER invoices_before_update
BEFORE UPDATE ON invoices
FOR EACH ROW
BEGIN
DECLARE sum_line_item_amount DECIMAL(9,2);
SELECT SUM(line_item_amount) INTO sum_line_item_amount
FROM invoice_line_items
WHERE invoice_id = NEW.invoice_id;
IF sum_line_item_amount != NEW.invoice_total THEN
SIGNAL SQLSTATE 'HY000'
SET MESSAGE_TEXT = 'Line item total must match invoice total.';
END IF;
END //
Modify it so that it also raises an error whenever the payment_total plus the credit_total becomes larger than the invoice_total in a row. Then test this trigger with an appropriate UPDATE statement.
Explanation / Answer
delimiter;
UPDATE invoices
SET payment_total =10976.06 , credit_total = 10976.06
WHERE invoice_id = 112 ;
SELECT invoice_id , invoice_total, invoice_total , credit_total , payment_total
WHERE invoice_id = 112;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.