A) Using MySQL commands answer the questions listed below usingthe Premier Products Company schema
- Using Triggers (5 pts.)
- Execute the following SQL to create the customer_audit table inthe premier schema.
CREATE TABLE IF NOT EXISTScustomer_audit (
customer_num CHAR(3) NOT NULL,
customer_name VARCHAR(35)NOT NULL,
street VARCHAR(15),
city VARCHAR(15),
state CHAR(2),
zip CHAR(5),
credit_limit DECIMAL(8,2),
date_changed DATETIME NOT NULL,
changed_by VARCHAR(45) NOT NULL);
- Notice that the audit table does not have a primary keydefined. Explain why this might be acceptable.
- Based on the current attributes in the customer_audit table,suggest a possible primary key and explain why it might beunique.
- Create a trigger that will insert a record into thecustomer_audit table when the customer data is changed. Only inserta new record if the attributes that are being audited change. Theattributes being audited are those that exist in the customer_audittable. For example the customer.balance attribute is not beingaudited so if it is the only attribute that changes as part of thetransaction then we do not need to insert a new record into theaudit table. Provide the SQL used to create this trigger.
- Write an SQL update statement that will change a customer andcause an audit record to be created. Provide the SQL statement youexecuted for this.
- Write an SQL statement that will display what is in thecustomer_audit table. Provide the SQL used.
Expert Answer
Answer to A) Using MySQL commands answer the questions listed below using the Premier Products Company schema Using Triggers (5 pt…