University:
California State University, Northridge
Course:
COMP 440 | Database Design
Academic year:

2024
Views:

126

Pages:

11
Author:

Riley K.

CREATE TRIGGER & PROCEDURE statement (8.26-8.30) A New Attempt Due Nov 22 by 11:59pm Points 20 Submitting a file upload File Types pdf Use the tables/data your created in previous assignment. 26. Create a trigger named trg updatecusthalance to update the CUST_BALANCE in the CUSTOMER table when a new invoice record is entered. (Assume that the sale is a credit sale.) Whatever value appears in the INV_AMOUNT column of the new invoice should be added to the customer's balance. Test the trigger using the following new INVOICE record, which would add 225.40 to the balance of customer 1001: 8005, 1001, '2022-04-27', 225.40 27. Write a procedure named pre_cust_add to add a new customer to the CUSTOMER table. Use the following values in the new record: 1002, 'Rauthor', 'Peter', 0.00 (You should execute the procedure and verify that the new customer was added to ensure your code is correct.) 28. Write a procedure named prc_invoice_add to add a new invoice record to the INVOICE table. Use the following values in the new record: 8006, 1000, '2022-04-30', 301.72 (You should execute the procedure and verify that the new invoice was added to ensure your code is correct.) 29. Write a trigger to update the customer balance when an invoice is deleted. Name the trigger trg updatecusthalance2. 30. Write a procedure to delete an invoice, giving the invoice number as a parameter. Name the procedure prc_inv_delete. Test the procedure by deleting invoices 8005 and 8006.