DBM405B Lab 6 in PL/SQL – Triggers – PL/SQL Script with 15 Screenshots – Instant Delivery – Perfect Solution

Lab Price = $23
Please feel free to send us your queries at: support@iqrajavaid.com
NOTE: THE QUERIES AND SCREENSHOTS ARE TAKEN IN PL/SQL DEVELOPER AND NOT IN SYSTEM Z.
Payment methods

Add to Cart

Buy Now

View Cart


Problem Statement

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

Relevant Material
Lab Price = $23
Please feel free to send us your queries at: support@iqrajavaid.com
Screenshots
Step4: Partd_AuditTABLE
Step4: Partd_AuditTABLE
Step1: Partd_SEQUENCE
Step1: Partd_SEQUENCE
Step4: Partc_StudentTABLE
Step4: Partc_StudentTABLE
Step5: Partd_AuditTABLE
Step5: Partd_AuditTABLE
Instructions
* If you want to purchase multiple products then click on “Buy Now” button which will give you ADD TO CART option.Please note that the payment is done through PayPal.
* You can also use 2CO option if you want to purchase through Credit Cards/Paypal but make sure you put the correct billing information otherwise you wont be able to receive any download link.
* Your paypal has to be pre-loaded in order to complete the purchase or otherwise please discuss it with us at support@iqrajavaid.com.
* As soon as the payment is received, download link of the solution will automatically be sent to the address used in selected payment method.
* Please check your junk mails as the download link email might go there and please be patient for the download link email. Sometimes, due to server congestion, you may receive download link with a delay.
* All the contents are compressed in one zip folder.
* In case if you get stuck at any point during the payment process, please immediately contact us at support@iqrajavaid.com and we will fix it with you.
* We try our best to reach back to you on immediate basis. However, please wait for atleast 8 hours for a response from our side. Afterall, we are humans.
* Comments/Feedbacks are truely welcomed and there might be some incentives for you for the next lab/quiz/assignment.
* In case of any query, please donot hesitate to contact us at support@iqrajavaid.com.
* MOST IMPORTANT Please use the tutorials as a guide and they need NOT to be used for any submission. Just take help from the material.
******************************************** Good Luck ***************************************************
Privacy Policy
We take your privacy seriously and will take all measures to protect your personal information.
Any personal information received will only be used to fill your order. We will not sell or redistribute your information to anyone.
Refund Policy
Incase you face any issues with the tutorial, please free to contact us on support@iqrajavaid.com
We will try our best to resolve the issue and if still persists we can discuss for a refund in case its required.
Payment Details
Lab Price = $23
Please feel free to send us your queries at: support@iqrajavaid.com
NOTE: THE QUERIES AND SCREENSHOTS ARE TAKEN IN PL/SQL DEVELOPER AND NOT IN SYSTEM Z.

Payment methods

Add to Cart

Buy Now

View Cart

Leave a Reply