Database Programming with PL/SQL
13-3: Creating DML Triggers: Part II
Practice Activities
Vocabulary
is a single trigger that can include actions for each of the four possible timing points - Compound Trigger
a trigger which replaces a DML statement on a complex view with DML statements on the tables on which the view is based - INSTEAD OF trigger
predefined Boolean variables INSERTING, DELETING and UPDATING which can be tested in a trigger body to take different code paths depending on which DML statement caused the trigger to fire - Conditional Predicate
enables a row trigger to access column values in the table row currently being modified by the triggering statement - :OLD and :NEW qualifiers
a DML trigger which fires once for each row affected by the triggering DML statement - DML Row Trigger
Try It / Solve It
1. 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;
CREATE OR REPLACE TRIGGER log_emp
AFTER INSERT ON employees BEGIN
INSERT INTO audit_table (action) VALUES ('Inserting');
END;
2. 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_EMP table each time.
CREATE OR REPLACE TRIGGER log_emp
AFTER INSERT OR DELETE ON employees BEGIN
IF INSERTING THEN
INSERT INTO audit_table (action) VALUES ('Inserting');
ELSIF DELETING THEN
INSERT INTO audit_table (action) VALUES ('Deleting');
END IF;
END;
INSERT INTO employees VALUES (58, 'Petre', 'Florin', 'pf@yahoo.com', '0732223223', TO_DATE(SYSDATE), 'AD_PRES', 3200, null, null, null, null);
DELETE FROM employees WHERE employee_id = 58;
SELECT * FROM audit_table;
3. Add a new column called emp_id to the AUDIT_EMP 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. INSERT and DELETE one or more
employees. Query the AUDIT_EMP table to see the audit trail.
ALTER TABLE audit_table
ADD emp_id NUMBER(3);
CREATE OR REPLACE TRIGGER log_emp
AFTER INSERT OR DELETE ON employees FOR EACH ROW BEGIN
IF INSERTING THEN
INSERT INTO audit_table (action, emp_id) VALUES ('Inserting', :NEW.employee_id);
ELSIF DELETING THEN
INSERT INTO audit_table (action, emp_id) VALUES ('Deleting', :OLD.employee_id);
END IF;
END;
INSERT INTO employees VALUES (58, 'Petre', 'Florin', 'pf@yahoo.com', '0732223223', TO_DATE(SYSDATE), 'AD_PRES', 3200, null, null, null, null);
DELETE FROM employees WHERE employee_id = 58;
SELECT * FROM audit_table;
4. To practice using INSTEAD OF triggers, complete the following steps.
A. Execute the following statement to create a table called DEPT_COUNT that keeps track
of how many employees are in each department.
CREATE TABLE dept_count
AS SELECT department_id dept_id, count(*) count_emps
FROM employees
GROUP BY department_id;
B. Execute the following statement to create a view of the EMPLOYEES table called
EMP_VU.
CREATE VIEW emp_vu
AS SELECT employee_id, last_name, department_id
FROM employees;
C. Create an INSTEAD OF row trigger on EMP_VU that increases the current count for a
department by 1 if a new employee is added and subtracts 1 from the count for a
department if an employee is deleted.
CREATE OR REPLACE TRIGGER count_trigger
INSTEAD OF INSERT OR DELETE ON emp_vu
BEGIN
IF DELETING THEN
UPDATE dept_count
SET count_emps = count_emps - 1
WHERE dept_id = :OLD.department_id;
ELSIF INSERTING THEN
UPDATE dept_count
SET count_emps = count_emps + 1
WHERE dept_id = :NEW.department_id;
END IF;
END;
D. Look at the counts for all departments in DEPT_COUNT. Test to see if your trigger fires
correctly by inserting a row into EMP_VU. Look at the count for the department of the new
employee. Delete a row from EMP_VU. Look at the count for the department where the
employee was just deleted.
SELECT * FROM dept_count;
dept_id 90 count 3
INSERT INTO emp_vu VALUES (58, 'Florin', 90);
SELECT * FROM dept_count;
dept_id 90 count 4
DELETE FROM emp_vu WHERE employee_id = 58;
SELECT * FROM dept_count;
dept_id 90 count 3
5. In this question, you will create a compound trigger. Once again, you will use the AUDIT_TABLE
you created in a previous exercise. If you have lost that table, below is the code to recreate it.
CREATE TABLE audit_table
(action VARCHAR2(50),
user_name VARCHAR2(30) DEFAULT USER,
last_change_date TIMESTAMP DEFAULT SYSTIMESTAMP,
emp_id NUMBER(6));
A. Create a compound trigger emp_audit_trigg on the EMPLOYEES table for the
following events: when updating the salary column of the EMPLOYEES table, enter
the value ‘Updating’ into the action column of the AUDIT_TABLE before the change
occurs. Next, once the action is complete, change the action to ‘Update complete; old
salary was (old_sal); new salary is (new_sal)’ where old_sal is the original salary
before the UPDATE, and new_sal is the new salary.
CREATE OR REPLACE TRIGGER emp_audit_trigg
FOR UPDATE OF salary ON employees
COMPOUND TRIGGER
log VARCHAR2(200);
BEFORE EACH ROW IS
BEGIN
INSERT INTO audit_table (action) VALUES ('Updating');
END BEFORE EACH ROW;
AFTER EACH ROW IS
BEGIN
log := 'Update complete; old salary was ' || TO_CHAR(:OLD.salary) || '; new salary is ' || TO_CHAR(:NEW.salary);
INSERT INTO audit_table (action) VALUES (log);
END AFTER EACH ROW;
END emp_audit_trigg;
B. Test your trigger by updating the salary of employee_id = 124 to be 1200, then querying
the AUDIT_TABLE to see that it contains a new row
UPDATE employees
SET salary = 1200
WHERE employee_id = 124;
0 Comments