1---- need a stored procedure for the Pizza Delivery Shop Practice Database

1. Script to Create the Database and Create the Required Tables being used from the Homework Database.
2. Script to insert necessary Test Data
3. A Functional Syntax Error Free Stored Procedure
4. A Statement to Invoke the Stored Procedure.
5. All the Script is contained in a single SQL Text File
6. The Script runs continuously without any Syntax Errors

 

 

 

2---- need a sequence of SQL Statements that include two Stored Triggers, SQL Statements to Create an Audit Trail Table that tracks Updating and Deleting Rows in Tables in your version for the Pizza Delivery Shop Practice Database, and DELETE and UPDATE Statements to invoke and test the Stored Triggers.

 

• need a Stored Trigger linked updating a table row for the Pizza Delivery Shop Practice Database
• need a Stored Trigger linked deleting a table row for the Pizza Delivery Shop Practice Database

 

For each Trigger, a copy of the row before the update or delete operation should be placed in one or more audit tables that keep track of the history of the data.

Your Script must contain SQL Statements that Test and Verify that the Triggers are working correctly. This means it must include the following  

• A Delete Statement removing one or more rows from a table
• An Update Statement changing one or more rows from a table
• CREATE TABLE Statement to create your version of the audit table storing the versions of the data before being deleted or updated
• SELECT Statements visually confirm the success of the operations.  This is a SELECT Statement listing the contents of the audit table or tables being used to track the original version of the instances before the modification or deletion.

 

1. Script to Create the Database and Create the Required Tables being used from the Pizza Delivery Shop Practice Database.
2. Script to insert necessary Test Data
3. Two Functional Syntax Error Free Stored Triggers
4. A DELETE Statement to Invoke the DELETE Stored Trigger
5. An UPDATE Statement to Invoke the UPDATE Stored Trigger
6. One or more SELECT Statements to verify the Execution of the Stored Triggers
7. All the Script is contained in a single SQL Text File
8. The Script runs continuously without any Syntax Errors

phone email pnum FK empid_FK email_FK date order_num type phone_FK order_num_FK auto_gen item_num_FK 000 item_num size price Customer Places M contains M Order Order Item 1 is a Pizza pnum name address -M M M auto_gen_FK pnum FK empid Takes Delivers item_num_FK 000 item_num name phone price Person Has pnum FK 000 isCustomer isOrderStaff O isDelivery Order Staff model Delivery Person Topping