Prior to beginning work on this assignment, review Chapter 8: Advanced SQL from the course text and the Using Triggers (Links to an external site.) presentation. This week you will develop the fourth task for the Online Food Ordering Database Project due this week.
For this assignment, you will continue to use PHPMyAdmin to
- Add a new table to your database. The table should be called tbl_employee_audit and should contain two fields (id, audit_data).
- Add for insertand delete triggers to the tbl_employee table.
The insert trigger is triggered when a new employee is added to the tbl_employee table. The trigger then takes the employee_id as a parameter and adds the following data as a new record to the tbl_employee_audit.
|
id |
audit_data |
|
1 |
New employee with ID = 1221 was added on Sep 21, 2016 |
The delete trigger is triggered when a row in the tbl_employee table is deleted. The trigger then takes employee_id as a parameter and adds the following data as a new record to the tbl_employee_audit.
|
id |
audit_data |
|
6 |
Employee with ID = 1221 was deleted on Sep 25, 2016 |
In your initial post,
- Attach a screen shot of the SQL query used to create the tbl_employee_audit, updated ER diagram, and SQL queries you used to generate the for insert and for delete triggers.
- Attach all the SQL queries you created for this assignment.
- Provide a short description of the use and different type of triggers in MySQL.
