USING SQL Write a procedure that automatically insert the paid invoices from inv
ID: 3607358 • Letter: U
Question
USING SQL
Write a procedure that automatically insert the paid invoices from invoices table into the paid_invoices table.
Inserted Invoices should be unique ( no duplicate invoices).
Write a function that check whether or not an invoice exists in paid_invoices.
The "INVOICES" and "PAID_INVOICES" have the same data types.
Only invoices paid in full ( Invoice_total = payment_total + credit_total) should be transferred to "PAID_INVOICES" table
**** EDIT ****
The invoice_id is a number like (33) and the Primary Key
The invoice_number is a varchar2 like ('Qb3295jd')
The same invoice_id can have multiple invoice_numbers
+NULLABLE DATA-DEFAULT )COLUMN-ID COMMENTS COLUMN NAME DATA TYPE NUMBER NUMBER 1 INVOICE ID 2 VENDOR ID 3 INVOICE_NUMBER VARCHAR2 (50 BYTE) No 4 INVOICE DATE 5 INVOICE TOTAL NUMBER (9,2) 6 PAYMENT_TOTAL NUMBER (9,2) 7 CREDIT TOTAL 8 TERMS ID 9 INVOICE DUE DATE DATE (null) (null) (null) (null) (null) 1 (null) 2 (null) 3 (null) 4 (null) 5 (null) 6 (null) 7 (null) 8 (null) 9 (null) 10 (null) No DATE No Yes Yes No No Yes NUMBER (9,2) NUMBER. (null) (null) (null) 10 PAYMENT DATE DATEExplanation / Answer
As you mentioned,"The same invoice_id can have multiple invoice_numbers" So your invoice_id cannot be primary key alone. it will be composite key which will be (invoice_id,invoice_numbers).
Please find procedure below which copies data from invoice table to paid_invoice table:
CREATE DEFINER=`root`@`localhost` PROCEDURE `rCopyPaidInvoices`()
BEGIN
SELECT * FROM invoices i1
WHERE INVOICE_TOTAL = (PAYMENT_TOTAL + CREDIT_TOTAL)
AND NOT EXISTS(
SELECT 1
FROM paid_invoices p1
WHERE p1.INVOICE_ID = i1.INVOICE_ID AND
p1.INVOICE_NUMBER = i1.INVOICE_NUMBER
)
END;
Function Checks if record exists in paid_invoice table or not:
CREATE DEFINER=`root`@`localhost` FUNCTION `fExistsPaidInvoice`(`invoiceID` INT, `invoiceNum` VARCHAR(50)) RETURNS tinyint(1)
BEGIN
IF( (SELECT COUNT(*) FROM paid_invoices WHERE INVOICE_ID = invoiceID AND INVOICE_NUMBER = invoiceNum) > 0) THEN RETURN true;
ELSE RETURN false;
END IF;
END;
If you wanted this to called automatically the you can create trigger:
CREATE TRIGGER `scheduledCopy`
AFTER INSERT ON `invoices`
FOR EACH ROW
CALL `rCopyPaidInvoices`()
Please feel free to ask if you any doubt and share the feedback too.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.