See detailed instruction in attachement and link providedhttps://dev.mysql.com/doc/refman/5.7/en/triggers.html

1 answer below »
See detailed instruction in attachement and link providedhttps://dev.mysql.com/doc/refman/5.7/en/triggers.html




Online Food Ordering Database – Task 5 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 insert”and “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.

Answered 3 days AfterJun 02, 2022

Answer To: See detailed instruction in attachement and link...

Mohd Abas answered on Jun 05 2022
78 Votes
Create table tbl_emp_audit:
Create Table tbl_emp_audit(
(
    Id int(11) Null,
    Audit_data varchar(80)
);
In
sert Trigger:
Working of For Insert trigger:
CREATE TRIGGER `for_insert`                                 AFTER INSERT ON `tbl_employee`                            FOR EACH ROW                                         INSERT INTO tbl_employee_audit(id,audit_data) VALUES(new.emp_id, CONCAT('New employee with ID = ', new.emp_id, ' was added on ‘ DATE_FORMAT(CURRENT_DATE, "%b %d, %Y")))
Delete Trigger:
CREATE TRIGGER for_delete                              AFTER DELETE ON                                     tbl_employee                                         FOR EACH ROW                                        INSERT INTO tbl_employee_audit(id,audit_data) VALUES (old.emp_id, CONCAT('New employee with ID = ', old.emp_id, ' was deleted on ', DATE_FORMAT(CURRENT_DATE, "%b %d, %Y")));
Working of for delete trigger:
ERD Diagrams:
Trigger:
In active databases, in order to maintain the general integrity a procedural code known as trigger is executed automatically on certain events. INSERT, UPDATE and DELETE are the commonly known events that can fire a trigger in the database for maintaining the integrity of the database.
As...
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here