Lab Assignment #13 – Triggers Section 13
CIS 208A PL/SQL
Section 13, Lesson 1 Exercise #1, 2, 3
1. What are triggers commonly used for in the database?
2. List the types of events that cause a database trigger to fire?
3. What is the most important thing to check before creating a database trigger?
Section 13, Lesson 2 Exercise #1, 2, 3, 4, 5
1. When creating a DML statement trigger on a table, what are the components that you must define?
2. A business rule states that each time one or more employees are added to the employees table, an audit record must also be created. This rule could be enforced using application code, but we have decided to enforce it using a DML statement trigger.
A. Create an audit table by executing the following SQL statement:
CREATE TABLE audit_table
(action VARCHAR2(15), user_name VARCHAR2(30) DEFAULT USER, last_change_date TIMESTAMP DEFAULT SYSTIMESTAMP);
B. Create a statement level trigger that inserts a row into the audit table immediately after one or more rows are added to the employees_dup table that you created in a previous lesson. The audit table row should contain value “Inserting” in the action column. The other two columns should have their default values. Save your trigger code for later.
C. Test your trigger by inserting a row into employees, then querying the audit table to see that it contains a row.
D. Make sure the trigger does not fire with a DELETE by deleting the employee you just entered. Recheck the audit_table table to make sure that there is not another new row.
3. What is the difference between a statement trigger and a row trigger?
4. A row trigger fires at least once even if no rows are affected. True or false?
5. Imagine that the following four DML triggers have been defined on the employees table:
a. a BEFORE INSERT statement trigger,
b. a BEFORE UPDATE statement trigger,
c. an AFTER UPDATE row trigger,
d. an AFTER DELETE statement trigger.
An UPDATE statement updates three employee rows. How many times will each trigger fire?
Section 13 Lesson 3, Exercise #1
1. Triggers:
A. Retrieve the code for the AFTER INSERT trigger you created in the previous practice, question 2B. If you have lost the code, here it is again:
CREATE OR REPLACE TRIGGER emp_audit_trigg
AFTER INSERT ON employees
BEGIN
INSERT INTO audit_table (action)
VALUES ('Inserting');
END;
B. Modify this trigger so that a DELETE on the employees table will fire the same trigger. Use the conditional predicates so an insert adds a row to the audit_emp table with
‘Inserted’ for the action column and a delete adds a row with ‘Deleted’ in the action column. Save the script and test your trigger by inserting an employee row and then deleting the same row, querying the audit table each time.
C. Add a new column called emp_id to the audit_table table. This column will contain the employee id of the worker whose record was inserted or deleted. Modify your trigger to be a row trigger so it will fire once for each row affected. The inserts into the audit_emp table should now include the employee id of the affected employee.
D. Test your trigger. First, turn off Autocommit in Application Express (you will need to rollback your changes later). Then, delete the three Sales Representatives (job_id = ‘SA_REP’). Query the audit table; you should see that three audit rows have been inserted. Finally, rollback your changes.
Section 13 Lesson 4, Exercise # 1, 2, 3, 5
1. What kind of event causes a DDL trigger to fire?
2. Explain the difference between ON SCHEMA and ON DATABASE triggers.
3. Imagine that the following audit table has been created in your schema:
CREATE TABLE audit_ddl
(action VARCHAR2(20),
who VARCHAR2(30) DEFAULT USER,
when TIMESTAMP DEFAULT SYSTIMESTAMP);
State which events would fire each of the following triggers:
a.
CREATE OR REPLACE TRIGGER new_tab_trig
AFTER CREATE ON SCHEMA
BEGIN
INSERT INTO audit_ddl(action)
VALUES( 'New object');
END;
b.
CREATE OR REPLACE TRIGGER drop_tab_trig
AFTER DROP ON SCHEMA
BEGIN
INSERT INTO audit_ddl(action)
VALUES( 'Deleted object');
END;
c.
CREATE OR REPLACE TRIGGER alter_tab_trig
AFTER ALTER ON SCHEMA
BEGIN
INSERT INTO audit_ddl(action)
VALUES(‘Modified object’);
END;
5. Create a row trigger:
A. That displays the maximum salary in the employees table, and is fired immediately before an employee’s salary is updated.
B. Test your trigger by attempting to update the salary of employee_id 100 to a new value of 25000. What happens and why?
Section 13 Lesson 5 Exercise #1, 2, 3, 4
1. Query the data dictionary to display information about all the triggers you created in earlier practices. The query should display the names, triggering event, trigger type, kind of object the trigger is on (table, view etc), name of the table or view, and the status.
2. Disable one of your triggers and re-query the data dictionary view. What is the trigger’s new status?
3. List the source code of the trigger that you just disabled.
4. Remove the trigger from the database and re-query the data dictionary to check that it has been removed.
Lab_13_Sp16.doc