A. Lab 6 of 7 : Triggers

B. Lab Overview – Scenario/Summary:

You have been asked to monitor actions on database objects using triggers.
Upon the completion of this lab, students will have hands-on experience using triggers in DB2.
C. Deliverables:

Lab Steps (all steps to be executed in System Z) Points
Step 1 Create an audit table 10 Pts.
Step 2 Create an insert trigger for the Student table 20 Pts.
Step 3 Create an update trigger for the Student table 20 Pts.
Step 4 Execute DML statements – Inserts 5 Pts.
Step 5 Execute DML statements – Updates 5 Pts.
Total Lab Points 60 Pts.

D. Lab Steps:

Step 1: Create an audit table.

a) You will be creating a table to capture auditing information on changes to the Student table. However, you will be placing this table in a new tablespace, and so you must create that tablespace first. Use the following command to create your new tablespace, replacing “S999999” with your username (but leaving the “Z” on the end of the name):

CREATE TABLESPACE S999999Z IN DB405B
USING STOGROUP DB405B PRIQTY 20 SECQTY 20 ERASE NO
LOCKSIZE PAGE LOCKMAX SYSTEM
BUFFERPOOL BP0 CLOSE NO COMPRESS YES;

b) Now, you will create your auditing table in this tablespace. Be sure to replace “S999999Z” with the name of the tablespace that you created:

CREATE TABLE AUDIT_TBL
(
AUDIT_NUM INTEGER PRIMARY KEY NOT NULL,
TABLE_NAME VARCHAR(50),
TRANS_TYPE VARCHAR(20),
TRANS_USER VARCHAR(20),
TRANS_NOTES VARCHAR(500),
TRANS_TS TIMESTAMP
) IN DB405B.S999999Z;

c) Create a sequence called AUDIT_SEQ that starts at 1 and increments by 1 – you will be using this to populate the AUDIT_NUM column.
d) Create a sequence called STUDENT_ID_SEQ that starts with 700 and increments by 10 – you will be using this to create SIDs for new students.

Step 2: Create an Insert Trigger for the STUDENT Table.

a) Create a trigger called tgr_student_bi to handle insertions into the STUDENT table.
b) The trigger should fire for each row inserted.
c) The trigger should automatically insert the student id using the sequence defined above; CURRENT DATE for the registration date, created and modified date, and USER for the person who created and modified the row.
d) The trigger should also write a record to the AUDIT_TBL, using AUDIT_SEQ to assign the AUDIT_NUM value. The audit record should record the name of the table being changed (Student), the transaction type (Insert), the current USER, the CURRENT TIMESTAMP, and a note to be constructed as follows (variable content in brackets):

Student [student id] added.

Step 3: Create an Update Trigger for the STUDENT Table.

a) Create a trigger that fires for every UPDATE on student, changing the MODIFIED_BY and MODIFIED_DATE columns for each affected row to USER and CURRENT DATE.
b) If the student’s last name has been changed, this trigger should also insert a row into AUDIT_TBL, using AUDIT_SEQ to assign the AUDIT_NUM value. The audit record should record the name of the table being changed (Student), the transaction type (Update), the current USER, the CURRENT TIMESTAMP, and a note to be constructed as follows (variable content in brackets):

Student [student id] last name changed from [old value] to [new value].

Step 4: Execute DML statements – Inserts.
Execute the following DML statements which should make use of your triggers:
a) Insert a new student with your_first_name01, your_last_name01; make up the other fields required by the record structure, but remember that some of the columns will be populated automatically by your insert trigger.
b) Insert another new student with your_first_name02, your_last_name02; make up the other fields required by the record structure, but remember that some of the columns will be populated automatically by your insert trigger.
c) Show the contents of the Student table for the students who you just inserted.
d) Show the contents of AUDIT_TBL.
Step 5: Execute DML statements – Updates.
Execute the following DML statements which should make use of your triggers:
a) Update the two student rows that you just inserted. Change the “01” record (from Step 4, Part A) last name to your_last_name01Updated (e.g., “Smith01Updated”).
b) Change the “02” record (from Step 4, Part b) last name to your_last_name01_updated (e.g., “Smith02Updated”).
c) Show the contents of the Student table for these two students.
d) Show the contents of AUDIT_TBL.
END OF LAB 6

For Submission into the Week 6 iLab Dropbox:
Zipped file containing:
• SQL PL code for each task
• Screenshots of output from executing each task