Database Programming with PL/SQL
13-2: Creating DML Triggers: Part I
Practice Activities
Vocabulary
fires once for each row affected by the triggering event. - Row trigger
A trigger which is automatically fired (executed) whenever a SQL DML statement (INSERT, UPDATE or DELETE) is executed. - DML trigger
is fired once on behalf of the triggering event, even if no rows are affected at all. - Statement trigger
Try It / Solve It
1. When creating a DML statement trigger on a table, what are the components that you must
define?
timing: BEFORE, AFTER or INSTEAD OF
event: INSERT, UPDATE, DELETE
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 the AUDIT_TABLE by executing the following SQL statement:
CREATE TABLE audit_table
(action VARCHAR2(50),
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 table. 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.
CREATE OR REPLACE TRIGGER log_emp
AFTER INSERT ON employees BEGIN
INSERT INTO audit_table (action) VALUES ('Inserting');
END;
C. Test your trigger by inserting a row into EMPLOYEES, then querying the AUDIT_TABLE to
see that it contains a row.
DESCRIBE employees; to see what we should insert
INSERT INTO employees VALUES (58, 'Petre', 'Florin', 'pf@yahoo.com', '0732223223', TO_DATE(SYSDATE), 'AD_PRES', 3200, null, null, null, null);
SELECT * FROM audit_table;
D. Make sure the trigger does not fire with a DELETE by deleting the employee you just entered.
Recheck the AUDIT_TABLE to make sure that there is not another new row.
SELECT * FROM employees WHERE employee_id = 58; to check that it's not deleted
SELECT * FROM audit_table;
3. True or false? A row trigger fires at least once even if no rows are affected.
What is the difference between a statement trigger and a row trigger?
False.
A row trigger fires every time when a row is affected.
A statement trigger fires only once when a statement modifies multiple rows.
A row trigger fires for every row that is affected.
4. Imagine that the following DML triggers have been defined on the EMPLOYEES table:
• A Before Insert statement trigger
• A Before Update statement trigger
• An After Delete statement trigger
An UPDATE statement updates three employee rows. How many times will each trigger fire?
5. Modify your AUDIT_TABLE trigger from question 2B so that it inserts a row into the audit table immediately
before one or more employee salaries are updated. The AUDIT_TABLE row should contain value
“Updating” in the action column.
Test your modified trigger by updating the salary of a non-existent employee (employee_id = 999),
then querying the AUDIT_TABLE to see that it contains a new row.
The update statement will fire once, before the 3 row updates.
CREATE OR REPLACE TRIGGER log_emp
BEFORE UPDATE ON employees BEGIN
INSERT INTO audit_table (action) VALUES ('Updating');
END;
UPDATE employees
SET first_name = 'Florin'
WHERE employee_id = 999;
SELECT * FROM audit_table;
Even if no rows are updated, the trigger already fired (before update).
A. Modify your trigger so that it prevents employees’ salaries being updated outside working
hours. The trigger should allow UPDATEs at other times (and still insert a row into the
AUDIT_TABLE), but should raise an application error if an update is attempted before 8:00 am
or after 6:00 pm on any day. (HINT: use HH24:MI to extract the time from SYSDATE).
CREATE OR REPLACE TRIGGER log_emp
BEFORE UPDATE ON employees BEGIN
INSERT INTO audit_table (action) VALUES ('Updating');
IF SYSDATE NOT BETWEEN TO_DATE('08:00','HH24:MI') AND TO_DATE('18:00','HH24:MI') THEN
RAISE_APPLICATION_ERROR(-20500,'You may insert into employees table only between 8 am and 6 pm');
END IF;
END;
B. You want to test your modified trigger. However, you need to make sure that right now the
database time is outside working hours. Remember that the database could be anywhere in
the world and therefore the database may not be in your time zone! Find the current database
time by executing:
SELECT TO_CHAR(SYSDATE,'HH24:MI') FROM dual;
If needed, modify your trigger so that it will raise the application error if you try to update a
salary within the next hour. For example, if the database time is 10:30, modify the trigger code
to include: …BETWEEN '10:30' AND '11:30'…
Test your modified trigger by trying to update the salary of employee_id 100 to a new value of
25000. You should see the ORA-20204 error message.
UPDATE employees
SET salary = 25000
WHERE employee_id = 100;
19:02
ORA-20204: You may insert into employees table only between 8 am and 6 pm
ORA-06512: at "RO_A851_SQL_S02.LOG_EMP", line 4
ORA-04088: error during execution of trigger 'RO_A851_SQL_S02.LOG_EMP'
2. SET salary = 25000
3. WHERE employee_id = 100;
0 Comments