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.

  1. 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.

  1. 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

   )

  1. 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.

  1. 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.

0 0
Add a comment Improve this question Transcribed image text
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;

Add a comment
Know the answer?
Add Answer to:
1. This trigger is from Figure 16.2 of the text. Copy and run this trigger so it is created in th...
Your Answer:

Post as a guest

Your Name:

What's your source?

Earn Coins

Coins can be redeemed for fabulous gifts.

Not the answer you're looking for? Ask your own homework help question. Our experts will answer your question WITHIN MINUTES for Free.
Similar Homework Help Questions
  • By using this table: CREATE TABLE Part ( Partid int IDENTITY PRIMARY KEY, Supplierid int NOT NULL REFERENCES Part (PartID), Description VARCHAR (25) NOT NULL, Count int NOT NULL, Class VARCHAR (5) NOT NULL, Inspection smalldatetime NOT NULL); I need to

    By using this table:CREATE TABLE Part (Partid int IDENTITY PRIMARY KEY,Supplierid int NOT NULLREFERENCES Part (PartID),Description VARCHAR (25) NOT NULL,Count int NOT NULL,Class VARCHAR (5) NOT NULL,Inspection smalldatetime NOT NULL);I need to write the following SQL statements: If it helps I am using MS Server 2008.1- Write a SQL statement that creates a stored procedure with one int input parameter. The stored procedure selects the supplierId field and the total of all Count field values for each group of supplierId's...

  • I am working on this code and keep getting errors like "ERROR: Syntax error: Encountered "<EOF>"...

    I am working on this code and keep getting errors like "ERROR: Syntax error: Encountered "<EOF>" at line 1, column 169." and "ERROR: Table/View 'STUDENT' does not exist. ERROR: Table/View 'STUDENT' does not exist. ERROR: Table/View 'STUDENT' does not exist." I do not know why this isn't working. Here is my code: DTCCDatabase.java import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; /** * This program creates the CoffeeDB database. */ public class DTCCDatabase {    public static void main(String[] args)...

  • Learning Objectives: Learn to define constraints on tables Lean to define primary keys and foreign keys...

    Learning Objectives: Learn to define constraints on tables Lean to define primary keys and foreign keys Exercise                                                                                In Lab 09, we will continue the practice of creating tables. You will need to write SQL statements for the tasks specified below. After you complete them, submit your SQL script (in one *.sql file) to Blackboard by 11:59pm Nov. 16 (Friday). Include your name as a comment in the SQL script submitted. The creation statement for each table is worth one point,...

  • PL/SQL Auction Program 1. Create a user xyz, who is the owner of the auction. Create...

    PL/SQL Auction Program 1. Create a user xyz, who is the owner of the auction. Create the schema, and package. 2. Create users x1 and x2 who are the participants in the auction. They will need acces to the package. 3. Bid on the same item and record your observations. Verify all scenarios. Upload the files with the missing code and a detailed sample run. AUCTION OWNER.TXT SQL> conn / as sysdba Connected. SQL> drop user xyz cascade; User dropped....

  • PHP Programming In this project, you will create a Web page that allows visitors to your...

    PHP Programming In this project, you will create a Web page that allows visitors to your site to sign a guest book that is saved to a database. Create a new document in your text editor and type the <!DOCTYPE> declaration, <html> element, document head, and <body> element. Use the strict DTD and “Guest Book” as the content of the <title> element. Add the following text and elements to the document body: <h2>Enter your name to sign our guest book</h2>...

  • * myCompany.SQL Introduction to SQL Script file for ORACLE DBMS This script file creates the following...

    * myCompany.SQL Introduction to SQL Script file for ORACLE DBMS This script file creates the following tables: VENDOR, PRODUCT, CUSTOMER, INVOICE, LINE EMPLOYEE and loads the default data rows */ set echo on; set serveroutput on; select systimestamp from dual; show user; ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY'; DROP TABLE LINE CASCADE CONSTRAINTS; DROP TABLE INVOICE CASCADE CONSTRAINTS; DROP TABLE CUSTOMER CASCADE CONSTRAINTS; DROP TABLE PRODUCT CASCADE CONSTRAINTS; DROP TABLE VENDOR CASCADE CONSTRAINTS; DROP TABLE EMPLOYEE CASCADE CONSTRAINTS; CREATE TABLE...

  • The lab for this week addresses taking a logical database design (data model) and transforming it...

    The lab for this week addresses taking a logical database design (data model) and transforming it into a physical model (tables, constraints, and relationships). As part of the lab, you will need to download the zip file titled CIS336Lab3Files from Doc Sharing. This zip file contains the ERD, Data Dictionary, and test data for the tables you create as you complete this exercise. Your job will be to use the ERD Diagram found below as a guide to define the...

  • could you please help me with this problem, also I need a little text so I...

    could you please help me with this problem, also I need a little text so I can understand how you solved the problem? import java.io.File; import java.util.Scanner; /** * This program lists the files in a directory specified by * the user. The user is asked to type in a directory name. * If the name entered by the user is not a directory, a * message is printed and the program ends. */ public class DirectoryList { public static...

  • Rationale The focus of the project is to develop your database programming skills. This project will...

    Rationale The focus of the project is to develop your database programming skills. This project will help you get a fair idea of the sales and distribution system in any organization that has a chain of Carrying and Forwarding Agents (CFAs) or super stockists and stockists. You will be able to implement database programming concepts of ADO.NET in VB.NET and ASP.NET to create a real-life, web-based database application. (VB stands for Visual Basic.) Scenario Smooth Pen, Inc., a pen manufacturing...

  • Using C++ in Visual Studios Rewrite the code to use a Stack instead of a Queue....

    Using C++ in Visual Studios Rewrite the code to use a Stack instead of a Queue. You will need to think about the differences in the Stack and Queue. Think about how they operate and how the nodes may differ.   Modify the code as necessary. You will need to push to the Stack, pop from the Stack, peek at the Stack. You will need a member functions like in the example code. Your program will need to show that everything...

ADVERTISEMENT
Free Homework Help App
Download From Google Play
Scan Your Homework
to Get Instant Free Answers
Need Online Homework Help?
Ask a Question
Get Answers For Free
Most questions answered within 3 hours.
ADVERTISEMENT
ADVERTISEMENT