[Solved]1 Trigger Figure 162 Text Copy Run Trigger Created Ap Database Run Update Test Notice Two Q37159665

1. This trigger is from Figure 16.2 ofthe text. Copy and run this trigger so it is created in the apdatabase. You can run the UPDATE below to test it. Notice twothings. The error message appears as a pink screen just like asyntax error would. Also, if you then query the invoices table, theUPDATE statement did not execute. The trigger prevented theerroneous UPDATE from changing the data.

               

DELIMITER //

CREATE TRIGGERinvoices_before_update

   BEFOREUPDATE ON invoices

   FOREACH ROW

BEGIN

  DECLARE sum_line_item_amount DECIMAL(9,2);

   SELECTSUM(line_item_amount)

      INTO sum_line_item_amount

   FROMinvoice_line_items

   WHEREinvoice_id = NEW.invoice_id;

   IFsum_line_item_amount != NEW.invoice_total

      THEN SIGNAL SQLSTATE ‘HY000’

      SET MESSAGE_TEXT = ‘Line item totalmust match invoice total.’;     END IF;

END//

DELIMITER ;

An UPDATE statement that fires thetrigger

UPDATE invoices

SET invoice_total =600 WHERE invoice_id = 100

You should get this message from thesystem

Error Code: 1644. Line item total mustmatch invoice total.

2. Open the trigger you created named invoices_before_update.(There is a tab along the top of phpMyAdmin for Triggers) Modify itso that it also raises an error whenever the payment total plus thecredit total becomes larger than the invoice total in a row. Then,test this trigger with an appropriate UPDATE statement. Includeyour testing UPDATE statement with your answer.

3. Create this table if it does not exist:

CREATE TABLEinvoices_audit

(

   vendor_id                INT            NOT NULL,

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

  action_date        DATETIME        NOT NULL

   )

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

5. Show the code to do the following:

Check whether the event scheduler isturned on.

If it isn’t, code a statement thatturns it on.

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

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

Expert Answer

---

Answer to 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 … . . .