DML Trigger Example
CREATE TRIGGER scott.emp_permit_changes
BEFORE
DELETE OR INSERT OR UPDATE
ON scott.emp
pl/sql_block
Oracle fires this trigger whenever a DELETE, INSERT, or UPDATE statement affects the EMP table in the schema SCOTT. The trigger EMP_PERMIT_CHANGES is a BEFORE statement trigger, so Oracle fires it once before executing the triggering statement.
DML Trigger Example with Restriction
CREATE TRIGGER scott.salary_check
BEFORE INSERT OR UPDATE OF sal, job ON scott.emp
FOR EACH ROW
WHEN (new.job <> 'PRESIDENT')
pl/sql_block
Oracle fires this trigger whenever one of the following statements is issued:
-an INSERT statement that adds rows to the EMP table
-an UPDATE statement that changes values of the SAL or JOB columns of the EMP table
SALARY_CHECK is a BEFORE row trigger, so Oracle fires it before changing each row that is updated by the UPDATE statement or before adding each row that is inserted by the INSERT statement.
SALARY_CHECK has a trigger restriction that prevents it from checking the salary of the company president.
Calling a Procedure in a Trigger Body Example
Assume we have defined a procedure SCOTT.CHECK_SAL, which verifies that an employee's salary in in an appropriate range. Then we could create the trigger SALARY_CHECK as follows:
CREATE TRIGGER scott.salary_check
BEFORE INSERT OR UPDATE OF sal, job ON scott.emp
FOR EACH ROW
WHEN (new.job <> 'PRESIDENT')
CALL check_sal(:new.job, :new.sal, :new.ename);
The procedure CHECK_SAL could be implemented in PL/SQL, C, or Java. Also, we can specify :OLD values in the CALL clause instead of :NEW values.
Database Event Trigger Example
This example creates a trigger to log all errors. The PL/SQL block does some special processing for a particular error (invalid logon, error number 1017). This trigger is an AFTER statement trigger, so it is fired after an unsuccessful statement execution (such as unsuccessful logon).
CREATE TRIGGER log_errors
AFTER SERVERERROR ON DATABASE
BEGIN
IF (IS_SERVERERROR (1017)) THEN
<special processing of logon error>
ELSE
<log error number>>
END IF;
END;
DDL Trigger Example
This example creates an AFTER statement trigger on any DDL statement CREATE. Such a trigger can be used to audit the creation of new data dictionary objects in our schema.
CREATE TRIGGER audit_db_object
AFTER CREATE
ON SCHEMA
pl/sql_block
INSTEAD OF Trigger Example
In this example, customer data is stored in two tables. The object view CUSTOMERS_ALL is created as a UNION of the two tables, CUSTOMERS_SJ and CUSTOMERS_PA. An INSTEAD OF trigger is used to insert values.
CREATE TABLE customers_sj
( cust NUMBER(6),
address VARCHAR2(50),
credit NUMBER(9,2) );
CREATE TABLE customers_pa
( cust NUMBER(6),
address VARCHAR2(50),
credit NUMBER(9,2) );
CREATE TYPE obj_customer AS OBJECT
( cust NUMBER(6),
address VARCHAR2(50),
credit NUMBER(9,2),
location VARCHAR2(20) );
CREATE VIEW CUSTOMERS_ALL(cust)
AS SELECT obj_customer (cust, address, credit, 'SAN_JOSE')
FROM customers_sj
UNION ALL
SELECT obj_customer (cust, address, credit, 'PALO_ALTO')
FROM customers_pa;
CREATE TRIGGER instrig INSTEAD OF INSERT ON CUSTOMERS_ALL
FOR EACH ROW
BEGIN
IF (:new.cust.location = 'SAN_JOSE') THEN
INSERT INTO customers_sj
VALUES (:new.cust.cust, :new.cust.address,:new.cust.credit);
ELSE
INSERT INTO customers_pa
VALUES (:new.cust.cust, :new.cust.address, :new.cust.credit);
END IF;
END;
Trigger
Logs Changes to EMPLOYEES.SALARY
Example: creates a log table and a trigger
that inserts a row in the log table after any UPDATE statement affects the SALARY column of the EMPLOYEES table, and then updates EMPLOYEES.SALARY and shows the
log table.
Create log table:
DROP TABLE
Emp_log;
CREATE TABLE
Emp_log (
Emp_id
NUMBER,
Log_date
DATE,
New_salary NUMBER,
Action
VARCHAR2(20));
Create trigger that inserts row in
log table after EMPLOYEES.SALARY is updated:
CREATE OR
REPLACE TRIGGER log_salary_increase
AFTER UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
INSERT INTO Emp_log (Emp_id, Log_date,
New_salary, Action)
VALUES (:NEW.employee_id, SYSDATE,
:NEW.salary, 'New Salary');
END;
/
Update EMPLOYEES.SALARY:
UPDATE
employees
SET salary =
salary + 1000.0
WHERE
Department_id = 20;
Result:
Show log table:
Result:
EMP_ID LOG_DATE
NEW_SALARY ACTION
---------- -------------------
-------------------- -------------------
201 13-JUL-17 15049.13
New Salary
202 13-JUL-17 6945.75
New Salary
2 rows
selected.
Conditional Trigger
Prints Salary Change Information
Example: creates a conditional trigger that
prints salary change information whenever a DELETE, INSERT, or UPDATE statement affects the EMPLOYEES table, unless that information is about the President.
The database evaluates the WHEN condition for
each affected row. If the WHEN condition is TRUE for an affected row, then the trigger fires for that
row before the triggering statement runs. If the WHEN condition is not TRUE for an affected row, then trigger does not fire for
that row, but the triggering statement still runs.
CREATE OR
REPLACE TRIGGER print_salary_changes
BEFORE DELETE OR INSERT OR UPDATE ON
employees
FOR EACH ROW
WHEN (NEW.job_id <> 'AD_PRES') -- do not print information about President
DECLARE
sal_diff
NUMBER;
BEGIN
sal_diff
:= :NEW.salary - :OLD.salary;
DBMS_OUTPUT.PUT(:NEW.last_name || ': ');
DBMS_OUTPUT.PUT('Old salary = ' ||
:OLD.salary || ', ');
DBMS_OUTPUT.PUT('New salary = ' ||
:NEW.salary || ', ');
DBMS_OUTPUT.PUT_LINE('Difference: ' ||
sal_diff);
END;
/
Query:
SELECT
last_name, department_id, salary, job_id
FROM
employees
WHERE
department_id IN (10, 20, 90)
ORDER BY
department_id, last_name;
Result:
LAST_NAME DEPARTMENT_ID SALARY JOB_ID
-------------------------
------------- ----------- --------------
Whalen 10 2800 AD_ASST
Fay 20
6000 MK_REP
Hartstein 20 13000 MK_MAN
De Haan 90 17000 AD_VP
King 90 24000 AD_PRES
Kochhar 90 17000 AD_VP
6 rows
selected.
Triggering statement:
UPDATE
employees
SET salary =
salary * 1.05
WHERE
department_id IN (10, 20, 90);
Result:
Whalen: Old
salary = 2800, New salary = 2940, Difference: 140
Hartstein:
Old salary = 13000, New salary = 13650, Difference: 650
Fay: Old
salary = 6000, New salary = 6300, Difference: 300
Kochhar: Old
salary = 17000, New salary = 17850, Difference: 850
De Haan: Old
salary = 17000, New salary = 17850, Difference: 850
6 rows
updated.
Query:
SELECT salary
FROM employees WHERE job_id = 'AD_PRES';
Result:
SALARY
----------
25200
1 row
selected.
Trigger Modifies LOB
Columns
DROP TABLE
tab1;
CREATE TABLE
tab1 (c1 CLOB);
INSERT INTO
tab1 VALUES ('<h1>HTML Document Fragment</h1><p>Some text.');
CREATE OR
REPLACE TRIGGER trg1
BEFORE UPDATE ON tab1
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('Old value of CLOB
column: '||:OLD.c1);
DBMS_OUTPUT.PUT_LINE('Proposed new value of
CLOB column: '||:NEW.c1);
:NEW.c1 := :NEW.c1 || TO_CLOB('<hr><p>Standard
footer paragraph.');
DBMS_OUTPUT.PUT_LINE('Final value of CLOB
column: '||:NEW.c1);
END;
/
SET
SERVEROUTPUT ON;
UPDATE tab1
SET c1 = '<h1>Different Document Fragment</h1><p>Different
text.';
SELECT * FROM
tab1;
Trigger with
REFERENCING Clause
Example: creates a table with the same name
as a correlation name, new, and then creates
a trigger on that table. To avoid conflict between the table name and the
correlation name, the trigger references the correlation name as Newest.
CREATE TABLE
new (
field1
NUMBER,
field2
VARCHAR2(20)
);
CREATE OR
REPLACE TRIGGER Print_salary_changes
BEFORE UPDATE
ON new
REFERENCING
new AS Newest
FOR EACH ROW
BEGIN
:Newest.Field2 := TO_CHAR (:newest.field1);
END;
/
OBJECT_VALUE
Pseudocolumn
A
trigger on an object table can reference the SQL pseudocolumn OBJECT_VALUE, which returns
system-generated names for the columns of the object table. The trigger can
also invoke a PL/SQL subprogram that has a formal IN parameter whose data type is OBJECT_VALUE.
Example: creates object table tbl, table tbl_history for logging
updates to tbl, and trigger Tbl_Trg. The trigger runs
for each row of tb1 that is
affected by a DML statement, causing the old and new values of the object t in tbl to be written
in tbl_history. The old and new
values are :OLD.OBJECT_VALUE and :NEW.OBJECT_VALUE.
Create, populate, and show object
table:
CREATE OR
REPLACE TYPE t AS OBJECT (n NUMBER, m NUMBER)
/
CREATE TABLE
tbl OF t
/
BEGIN
FOR j IN 1..5 LOOP
INSERT INTO tbl VALUES (t(j, 0));
END LOOP;
END;
/
SELECT * FROM
tbl ORDER BY n;
Result:
N M
----------
----------
1
0
2 0
3 0
4 0
5 0
5 rows
selected.
Create history table and trigger:
CREATE TABLE
tbl_history ( d DATE, old_obj t, new_obj t)
/
CREATE OR
REPLACE TRIGGER Tbl_Trg
AFTER UPDATE ON tbl
FOR EACH ROW
BEGIN
INSERT INTO tbl_history (d, old_obj, new_obj)
VALUES (SYSDATE, :OLD.OBJECT_VALUE, :NEW.OBJECT_VALUE);
END Tbl_Trg;
/
Update object table:
UPDATE tbl
SET tbl.n = tbl.n+1
/
Result:
Show old and new values:
BEGIN
FOR j IN (SELECT d, old_obj, new_obj FROM
tbl_history) LOOP
DBMS_OUTPUT.PUT_LINE (
j.d ||
' -- old: ' || j.old_obj.n || ' ' ||
j.old_obj.m ||
' -- new: ' || j.new_obj.n || ' ' ||
j.new_obj.m
);
END LOOP;
END;
/
Result:
13-JUL-17 --
old: 1 0 -- new: 2 0
13-JUL-17 --
old: 2 0 -- new: 3 0
13-JUL-17 --
old: 3 0 -- new: 4 0
13-JUL-17 --
old: 4 0 -- new: 5 0
13-JUL-17 --
old: 5 0 -- new: 6 0
All values of column n were
increased by 1. The value of m remains 0.
I hope you all have enjoyed reading this article. Comments are welcome....
Related Posts:
