Step 1: Create table audits via triggers The system must log any insertion, dele

Question

Step 1: Create table audits via triggers

The system must log any insertion, deletion, or updates to the following tables:

• Employee table (project 1)

• Job table (project 1)

• ProjectMain table (Project 2)

• ActivityMain table (Project 2)

For each one of the table above, you will write the SQL Script to create their respective AUDIT table which is a table that contains the same columns as the original table, plus the additional columns Operation and DateTimeStamp. For example, for the EMPLOYEE table with the given columns, you will create EMPLOYEEAUDIT:

• empNumber (char(8)), - empNumber (char(8)),

• firstName (varchar(25)), - firstName (varchar(25)),

• lastName varchar(25)), - lastName varchar(25)),

• ssn (char(9)), - ssn (char(9)),

• address (varchar(50)), - address (varchar(50)),

• state (char(2)), - state (char(2)),

• zip (char(5)), - zip (char(5)),

• jobCode (char(4)) , - jobCode (char(4)) ,

• dateOfBirth (date), - dateOfBirth (date),

• certification(bit), - certification(bit),

• salary(money) ) - salary(money) )

• - Operation (varchar(50))

• - DateTimeStamp (datetime)

Therefore, your assignment script will create the following 4 Audit tables :

- EmployeeAudit

- JobAudit

- ProjectMainAudit

- ActivityMainAudit

NOTE: You MUST use the above names for the audit tables.

You will create the following 4 triggers:

- trgEmployee: Will be placed on the Employee table and listens for Inserts, Deletes, and Updates

- trgJob: Will be placed on the Job table and listens for Inserts, Deletes, and Updates

- trgProjectMain: Will be placed on the ProjectMain table that contains the projectId and projectName and listens for Inserts, Deletes, and Updates

- trgActivityMain: Will be placed on the ActivityMain table that contains the activityId and activityName and listens for Inserts, Deletes, and Updates.

Again, each trigger will write to its respective audit table:

trgProjectMain will write to ProjectMainAudit

trgActivityMain will write to ActivityMainAudit

trgEmployee will write to EmployeeAudit

trgJob will write to JobAudit

Again, the columns which will be written to the audit tables will be all the original columns plus “Operation” and “DateTimeStamp”

The trigger will support the Insert, Delete and Update operation as follows:

• If a record is inserted in the original table, then the audit table will contain the original values plus “INSERTED” into the operation column with the datetime stamp.

• For the Delete operation, it will contain “DELETED” in the operation column plus the datetimestamp.

• For the Update operation, it will contain 2 records in the audit table, one for the old values (Operation will have DELETED) and one for the new values (Operation will have INSERTED).

Explanation / Answer

You haven't provided the database system that you are using. So I provided a generic answer. Might have to adapt a bit depending on database used (oracle/MySQL/PostgreSQL/Teradata).
You haven't provided details of the next three tables. You have to put in the proper column names for those tables and triggers.

CREATE TABLE EMPLOYEEAUDIT (empNumber CHAR(8), firstName VARCHAR(25), lastName VARCHAR(25), ssn CHAR(9), address VARCHAR(50), state CHAR(2), zip CHAR(2), jobCode CHAR(4), dateOfBirth DATE, certification BIT, salary DECIMAL(15,2), Operation VARCHAR(50), DateTimeStamp DATETIME);

CREATE TRIGGER TRGEMPLOYEE ON INSERT OR UPDATE OR DELETE ON EMPLOYEE
   IF INSERTING THEN
       FOR EACH ROW
       BEGIN
           INSERT INTO EMPLOYEEAUDIT (empNumber, firstName, lastName, ssn, address, state, zip, jobCode, dateOfBirth, certification, salary, Operation, DateTimeStamp) VALUES (NEW.empNumber, NEW.firstName, NEW.lastName, NEW.ssn, NEW.address, NEW.state, NEW.zip, NEW.jobCode, NEW.dateOfBirth, NEW.certification, NEW.salary, 'INSERTED', NOW());
       END;
   ELSEIF UPDATING THEN
       FOR EACH ROW
       BEGIN
           INSERT INTO EMPLOYEEAUDIT (empNumber, firstName, lastName, ssn, address, state, zip, jobCode, dateOfBirth, certification, salary, Operation, DateTimeStamp) VALUES (OLD.empNumber, OLD.firstName, OLD.lastName, OLD.ssn, OLD.address, OLD.state, OLD.zip, OLD.jobCode, OLD.dateOfBirth, OLD.certification, OLD.salary, 'DELETED', NOW());
           INSERT INTO EMPLOYEEAUDIT (empNumber, firstName, lastName, ssn, address, state, zip, jobCode, dateOfBirth, certification, salary, Operation, DateTimeStamp) VALUES (NEW.empNumber, NEW.firstName, NEW.lastName, NEW.ssn, NEW.address, NEW.state, NEW.zip, NEW.jobCode, NEW.dateOfBirth, NEW.certification, NEW.salary, 'INSERTED', NOW());
       END;
   ELSEIF DELETING THEN
       FOR EACH ROW
       BEGIN
           INSERT INTO EMPLOYEEAUDIT (empNumber, firstName, lastName, ssn, address, state, zip, jobCode, dateOfBirth, certification, salary, Operation, DateTimeStamp) VALUES (OLD.empNumber, OLD.firstName, OLD.lastName, OLD.ssn, OLD.address, OLD.state, OLD.zip, OLD.jobCode, OLD.dateOfBirth, OLD.certification, OLD.salary, 'DELETED', NOW());
       END;
   END IF;
  
CREATE TABLE JOBAUDIT (col1 type, ... , Operation VARCHAR(50), DateTimeStamp DATETIME);

CREATE TRIGGER TRGJOB ON INSERT OR UPDATE OR DELETE ON JOB
   IF INSERTING THEN
       FOR EACH ROW
       BEGIN
           INSERT INTO JOBAUDIT (col1, ... , Operation, DateTimeStamp) VALUES (NEW.col1, ... , 'INSERTED', NOW());
       END;
   ELSEIF UPDATING THEN
       FOR EACH ROW
       BEGIN
           INSERT INTO JOBAUDIT (col1, ... , Operation, DateTimeStamp) VALUES (OLD.col1, ... , 'DELETED', NOW());
           INSERT INTO JOBAUDIT (col1, ... , Operation, DateTimeStamp) VALUES (NEW.col1, ... , 'INSERTED', NOW());
       END;
   ELSEIF DELETING THEN
       FOR EACH ROW
       BEGIN
           INSERT INTO JOBAUDIT (col1, ... , Operation, DateTimeStamp) VALUES (OLD.col1, ... , 'DELETED', NOW());
       END;
   END IF;

CREATE TABLE PROJECTMAINAUDIT (col1 type, ... , Operation VARCHAR(50), DateTimeStamp DATETIME);
  
CREATE TRIGGER TRGPROJECTMAIN ON INSERT OR UPDATE OR DELETE ON PROJECTMAIN
   IF INSERTING THEN
       FOR EACH ROW
       BEGIN
           INSERT INTO PROJECTMAINAUDIT (col1, ... , Operation, DateTimeStamp) VALUES (NEW.col1, ... , 'INSERTED', NOW());
       END;
   ELSEIF UPDATING THEN
       FOR EACH ROW
       BEGIN
           INSERT INTO PROJECTMAINAUDIT (col1, ... , Operation, DateTimeStamp) VALUES (OLD.col1, ... , 'DELETED', NOW());
           INSERT INTO PROJECTMAINAUDIT (col1, ... , Operation, DateTimeStamp) VALUES (NEW.col1, ... , 'INSERTED', NOW());
       END;
   ELSEIF DELETING THEN
       FOR EACH ROW
       BEGIN
           INSERT INTO PROJECTMAINAUDIT (col1, ... , Operation, DateTimeStamp) VALUES (OLD.col1, ... , 'DELETED', NOW());
       END;
   END IF;

CREATE TABLE ACTIVITYMAINAUDIT (col1 type, ... , Operation VARCHAR(50), DateTimeStamp DATETIME);
  
CREATE TRIGGER TRGACTIVITYMAIN ON INSERT OR UPDATE OR DELETE ON ACTIVITYMAIN
   IF INSERTING THEN
       FOR EACH ROW
       BEGIN
           INSERT INTO ACTIVITYMAINAUDIT (col1, ... , Operation, DateTimeStamp) VALUES (NEW.col1, ... , 'INSERTED', NOW());
       END;
   ELSEIF UPDATING THEN
       FOR EACH ROW
       BEGIN
           INSERT INTO ACTIVITYMAINAUDIT (col1, ... , Operation, DateTimeStamp) VALUES (OLD.col1, ... , 'DELETED', NOW());
           INSERT INTO ACTIVITYMAINAUDIT (col1, ... , Operation, DateTimeStamp) VALUES (NEW.col1, ... , 'INSERTED', NOW());
       END;
   ELSEIF DELETING THEN
       FOR EACH ROW
       BEGIN
           INSERT INTO ACTIVITYMAINAUDIT (col1, ... , Operation, DateTimeStamp) VALUES (OLD.col1, ... , 'DELETED', NOW());
       END;
   END IF;  

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.