Question

1. This trigger is from Figure 16.2 of the text. Copy and run this trigger so it is created in the ap database. You can run the UPDATE below to test it. Notice two things. The error message appears as a pink screen just like a syntax error would. Also, if you then query the invoices table, the UPDATE statement did not execute. The trigger prevented the erroneous UPDATE from changing the data.

               

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//

DELIMITER ;

An UPDATE statement that fires the trigger

UPDATE invoices

SET invoice_total = 600 WHERE invoice_id = 100

You should get this message from the system

Error Code: 1644. Line item total must match invoice total.

2. Open the trigger you created named invoices_before_update. (There is a tab along the top of phpMyAdmin for Triggers) 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. Include your testing UPDATE statement with your answer.

3. Create this table if it does not exist:

CREATE TABLE invoices_audit

(

    vendor_id                 INT             NOT NULL,

    invoice_number      VARCHAR(50)     NOT NULL,     invoice_total            DECIMAL(9,2)    NOT NULL,       action_type         VARCHAR(50)     NOT NULL,

   action_date         DATETIME        NOT NULL

   )

4. Create a trigger named invoices_after_update. This trigger should insert the old data about the invoice into the Invoices_Audit table after the row is updated. Then, test this trigger with an appropriate UPDATE statement. Include your UPDATE statement with your answer.

5. Show the code to do the following:

Check whether the event scheduler is turned on.

If it isn’t, code a statement that turns it on.

Then, create an event that inserts a test row that contains test values into the Invoices_Audit table every minute. To make sure that this event has been created, code a SHOW EVENTS statement that views this event and a SELECT statement that views the data that’s inserted into the Invoices_Audit table. (provide screenshot)

Once you’re sure this event is working correctly, code a DROP EVENT statement that drops the event.

Answer 1

--1- updates trigger invoices_before_update to raise an error whenever payment total + credit total > invoice total
drop trigger if exists invoices_before_update;

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.';

   elseif new.payment_total + new.credit_total > old.invoice_total then
       signal sqlstate 'HY000'
           set message_text = 'Payment and credit total cannot exceed invoice total.';
   end if;

end //

delimiter ;

UPDATE invoices
SET payment_total = 10976.06, credit_total = 10976.06
WHERE invoice_id = 112;

SELECT invoice_id, invoice_total, credit_total, payment_total
FROM invoices
WHERE invoice_id = 112;

#2 - trigger: invoices_after_update
#creates invoices_audit table
#inserts old data into the invoices_audit table

-- creates invoices_audit table
use ap;

create table invoices_audit
(
   vendor_id       int               not null,
   invoice_number   varchar(50)       not null,
   invoice_total   decimal(9,2)   not null,
   action_type       varchar(50)       not null,
   action_date       datetime       not null
);

drop trigger if exists invoices_after_update;

delimiter //

create trigger invoices_after_update
   after update on invoices
   for each row
begin
   insert into invoices_audit values
   (old.vendor_id, old.invoice_number, old.invoice_total, 'updated', now());
end //

delimiter ;

update invoices
set payment_total = 50
where invoice_id = 7;

select * from invoices_audit

#3
USE ap;

SET GLOBAL event_scheduler = ON;

DROP EVENT IF EXISTS minute_test;

DELIMITER //

CREATE EVENT minute_test
ON SCHEDULE EVERY 1 MINUTE
DO BEGIN
    INSERT INTO invoices_audit VALUES
    (9999, 'test', 999.99, 'INSERTED', NOW());
END//

DELIMITER ;

SHOW EVENTS LIKE '%min';

SELECT * FROM invoices_audit;

DROP EVENT minute_test;