Each question has been broken into three sections with ...

[Pages:8]Each question has been broken into three sections with full code of SQL file at the end of this document.

A) The Trigger Code/Stored Procedure Code B) Screenshots from SQL Developer showing the code C) Screenshots from each of the test queries with explanations of what changed. Trigger 1 ? A) Trigger code/Stored Procedure Code

-- Stored Procedure

--TRIGGER 1 CREATE OR REPLACE TRIGGER EMPDEPTFK_UPDATE BEFORE UPDATE ON Department

... Trigger/Stored Procedure Code and Successfully Run Snapshot here ...

Trigger 1-1 ? C) Screenshots of Output and explanation of changes

Step 1: SELECT * FROM DEPARTMENT; ?- this is the initial table

Step 2: SELECT * FROM EMPLOYEE; ? this is the initial table

Step 3: Update Event that will fire the Trigger UPDATE DEPARTMENT SET DNUMBER = 99 WHERE DNUMBER = 4; This will activate the update trigger. SELECT * FROM DEPARTMENT Notice DNUMBER has change from 4 to 99 activating the trigger.

SELECT * FROM EMPLOYEE Notice the DNO has changed to 99 for all employees that were in DNO 4 because the FK constraint indicates ON UPDATE CASCADE

Step 4 Delete Event that will fire the Trigger DELETE FROM DEPARTMENT WHERE DNUMBER = 5; 1 row deleted. SELECT * FROM DEPARTMENT Notice the DNUMBER = 5 was deleted which activated the trigger.

SELECT * FROM EMPLOYEE Notice the employees from DNO = 5 are not in DNO = 1 which is the default value because the fk constraint indicated ON DELETE SET DEFAULT

STEP 5 DROP TRIGGER EMPDEPTFK; trigger EMPDEPTFK dropped.

Trigger 2 ? A) Trigger code here

--TRIGGER 2 CREATE OR REPLACE TRIGGER AUDITWORKSON BEFORE INSERT OR UPDATE OR DELETE ON WORKS_ON

...

Trigger 2 ? B) Screenshots of SQL Developer

Trigger 2 ? C) Screenshots of Output and explanation of changes

Step 1: Initial Table Values SELECT * FROM WORKS_ON; -- Initial Table Values

SELECT * FROM AUDIT_WORKS_ON; -- Initial Table Values

Step 2: Insert a record into WORKS_ON INSERT INTO WORKS_ON(ESSN, PNO, HOURS) VALUES (999663333, 10, 4); SELECT * FROM WORKS_ON; Notice the new record was inserted which activated trigger.

SELECT * FROM AUDIT_WORKS_ON; Notice the audit table had a record added from the trigger event. The old values are set to null since this is a new record.

Step 3: Update a record on WORKS_ON UPDATE WORKS_ON SET PNO = 222 WHERE PNO=2; SELECT * FROM WORKS_ON; Notice the records where PNO = 2 were updated to 222 activating the trigger.

SELECT * FROM AUDIT_WORKS_ON; Notice the audit table had 4 records added from the trigger event. The old and new values are captured.

Step 4: Delete a record on WORKS_ON DELETE FROM WORKS_ON WHERE PNO=1; SELECT * FROM WORKS_ON; Notice the records where PNO = 1 were deleted activating the trigger.

SELECT * FROM AUDIT_WORKS_ON; Notice the audit table had a record added from the trigger event. The new values are set to null because the record was deleted.

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download