Following question uses MySQL. Uses AP database. Creating after triggers are my

Question

Following question uses MySQL. Uses AP database. Creating after triggers are my main problem for this question.

1. The following trigger automatically generates a shipping label as soon as the PaymentTotal column of the Invoice total is updated. Such a trigger may be useful for mailing updated/paid Invoices to customers.

Create the ShippingLabel table first in your AP database. Then write an AFTER trigger to insert data in the ShippingLabel table with data from the Vendors table. In order to get the VendorID that is being updated, you need to select the VendorID from the virtual Inserted table.

The updated row of data is stored in a virtual table called Inserted. Fill the table below.

Invoice
ID

Vendor
ID

Invoice
Number

Invoice
Date

Invoice
Total

Payment
Total

Credit
Total

Terms
ID

Invoice
Due
Date

Payment
Date

Invoice
ID

Vendor
ID

Invoice
Number

Invoice
Date

Invoice
Total

Payment
Total

Credit
Total

Terms
ID

Invoice
Due
Date

Payment
Date

Create a trigger for the Invoices table that automatically inserts the vendor name and address for a paid invoice into a table named Shipping Labels. The trigger should fire any time the Payment Total column of the Invoices table is updated. The structure of the ShippingLabels table is as follows: CREATE TABLE ShippingLabels varchar (50) (VendorName vendor Address1 varchar (50), vendor Address2 varchar (50), Vendor City varchar (50) vendor State char (2), vendor ZipCode varchar (20) Use this UPDATE statement to test the trigger: UPDATE Invoices SET Payment Total 67.92 PaymentDate 2012-04-23 WHERE InvoiceID 100

Explanation / Answer

First create following Table

CREATE TABLE `test`.`ShippingLabels` (
`VenderName` VARCHAR(50) NOT NULL,
`VenderAddress1` VARCHAR(50) NOT NULL,
`VenderAddress2` VARCHAR(50) NOT NULL,
`VenderCity` VARCHAR(50) NOT NULL,
`VenderState` VARCHAR(20) NOT NULL,
`VenderZipCode` VARCHAR(20) NOT NULL,
PRIMARY KEY (`VenderName`)
)
ENGINE = InnoDB;

CREATE TABLE `test`.`Vendors` (
`VendorId` INTEGER NOT NULL,
`VendorName` VARCHAR(50) NOT NULL,
`VendorAddress1` VARCHAR(50) NOT NULL,
`VendorAddress2` VARCHAR(50) NOT NULL,
`VendorCity` VARCHAR(50) NOT NULL,
`VendorState` VARCHAR(20) NOT NULL,
`VendorZipCode` VARCHAR(20) NOT NULL,
PRIMARY KEY (`VendorId`)
)
ENGINE = InnoDB;

CREATE TABLE `test`.`Invoices` (
`InvoicesId` INTEGER NOT NULL,
`VenderId` INTEGER NOT NULL,
`InvoicesNumber` INTEGER NOT NULL,
`InvoicesDate` VARCHAR(45) NOT NULL,
`InvoicesTotal` INTEGER ZEROFILL NOT NULL,
`PaymentTotal` INTEGER ZEROFILL NOT NULL,
`CreditTotal` INTEGER ZEROFILL NOT NULL,
`TermsId` INTEGER NOT NULL,
`InvoicesDueDate` VARCHAR(45) NOT NULL,
`PaymentDate` VARCHAR(45) NOT NULL,
PRIMARY KEY (`InvoicesId`)
)
ENGINE = InnoDB;

we have Invoices Table:
mysql> select * from Invoices;
+------------+----------+----------------+--------------+---------------+--------------+-------------+---------+-----------------+-------------+
| InvoicesId | VenderId | InvoicesNumber | InvoicesDate | InvoicesTotal | PaymentTotal | CreditTotal | TermsId | InvoicesDueDate | PaymentDate |
+------------+----------+----------------+--------------+---------------+--------------+-------------+---------+-----------------+-------------+
| 100 | 10 | 111 | 15-3-2017 | 0000000002 | 0000010000 | 0000020000 | 5 | 16-03-2017 | 17-03-2017 |
+------------+----------+----------------+--------------+---------------+--------------+-------------+---------+-----------------+-------------+
1 row in set (0.00 sec)

We have Vendors Table:
mysql> select * from Vendors;
+----------+------------+----------------+----------------+------------+--------------+---------------+
| VendorId | VendorName | VendorAddress1 | VendorAddress2 | VendorCity | VendorState | VendorZipCode |
+----------+------------+----------------+----------------+------------+--------------+---------------+
| 10 | JON | xyz | pqr | alaska | WashingtonDc | 44444 |
+----------+------------+----------------+----------------+------------+--------------+---------------+
1 row in set (0.00 sec)

Trigger this command to test:

UPDATE Ivoices SET PaymentTotal=16.97,PaymentDate="2012-04-23" where InvoicesId=100;
mysql> select * from Invoices;
+------------+----------+----------------+--------------+---------------+--------------+-------------+---------+-----------------+-------------+
| InvoicesId | VenderId | InvoicesNumber | InvoicesDate | InvoicesTotal | PaymentTotal | CreditTotal | TermsId | InvoicesDueDate | PaymentDate |
+------------+----------+----------------+--------------+---------------+--------------+-------------+---------+-----------------+-------------+
| 100 | 10 | 111 | 15-3-2017 | 0000000002 | 0000000017 | 0000020000 | 5 | 16-03-2017 | 2012-04-23 |
+------------+----------+----------------+--------------+---------------+--------------+-------------+---------+-----------------+-------------+
1 row in set (0.00 sec)

After trigering this command it will copy data automatically from vendors table to shippinglabels:
INSERT INTO shippinglabels (VenderName, VenderAddress1,VenderAddress2,VenderCity,VenderState ,VenderZipCode)
SELECT VendorName, VendorAddress1,VendorAddress2,VendorCity,VendorState ,VendorZipCode FROM Vendors where Vendorid=10;
mysql> select * from shippinglabels;
+------------+----------------+----------------+------------+--------------+---------------+
| VenderName | VenderAddress1 | VenderAddress2 | VenderCity | VenderState | VenderZipCode |
+------------+----------------+----------------+------------+--------------+---------------+
| JON | xyz | pqr | alaska | WashingtonDc | 44444 |
+------------+----------------+----------------+------------+--------------+---------------+
1 row in set (0.00 sec)

Related Questions

Navigate

• Browse (All)
• Browse F
• Subjects

---