|
“You can tell a lot
about a fellow's character by his way of eating jellybeans.”
Ronald Reagan (1911 - ), quoted in Observer, March 29 1981 |
Database Triggers
Introduction
This example shows that
Oracle fires the "schema.check_salary" trigger whenever a
UPDATE or INSERT statement affects the "SAL" column on the
"EMP" table, if and only if its value is more than 6000
dollars.
DML trigger
The trigger writes a
message into "audit_table" that at such day a user inserted
or update such column. For example: SCOTT inserted employee number:
120 at JUN 10, 2003.
(Procedure Builder)
SQL> CREATE OR REPLACE
TRIGGER check_salary
BEFORE INSERT OR UPDATE OF
sal ON emp
REFERENCING OLD AS old NEW
AS new
FOR EACH ROW
WHEN (new.sal > 6000)
DECLARE
-- no variables...
BEGIN
IF INSERTING
THEN
INSERT INTO audit_table
VALUES
(USER || ' inserted
employee number:'
|| :new.empno || ' ' || '
at:' || sysdate);
-- no commit needed...
ELSIF UPDATING
THEN
INSERT INTO audit_table
VALUES
(USER || ' updated employee
number:'
|| :new.empno || ' ' || '
at:' || sysdate);
-- no commit needed...
END IF;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR (
num => -20000,
msg => 'Cannot drop
object');
END check_salary;
/
DDL Trigger
This example shows that a
DBA is monitoring or checking when and who creates or drops an object
in the database.
(Procedure Builder)
SQL> CREATE TRIGGER
check_who_create_objects
AFTER CREATE OR DROP ON
SCHEMA
BEGIN
INSERT INTO audit_table
VALUES
(USER || ' created an
object on: '
|| sysdate);
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR (
num => -20000,
msg => 'Cannot drop
object');
END;
/
Database Event Trigger
This example shows that a
DBA is monitoring who and when shuts down a database.
(Procedure Builder)
SQL> CREATE TRIGGER
check_who_shutdown_database
BEFORE SHUTDOWN ON DATABASE
BEGIN
INSERT INTO audit_table
VALUES
(USER || ' shutdown the
database on: '
|| sysdate);
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR (
num => -20000,
msg => 'Cannot drop
object');
END;
/
INSTEAD OF trigger:
This example shows that how
you can use an "INSTEAD OF" triger.
SQL> CREATE OR REPLACE
VIEW dept_employees AS
SELECT dname, ename
FROM emp, dept
WHERE dept.deptno =
emp.deptno
/
Normally this view would
not be updatable, because the primary key of the department (dept)
table (deptno) is not unique in the result set of the join view. To
make this view updatable, you should create an INSTEAD OF trigger on
the view to process INSERT statements directed to the view.
SQL> CREATE SEQUENCE
seq_deptno
START WITH 60
INCREMENT BY 10;
CREATE OR REPLACE TRIGGER
insert_dept_emp_info
INSTEAD OF INSERT ON
dept_employees
DECLARE
duplicate_info EXCEPTION;
PRAGMA EXCEPTION_INIT (duplicate_info,
-00001);
BEGIN
INSERT INTO dept
VALUES
(seq_deptno.nextval,:new.dname,'No
location yet');
EXCEPTION
WHEN duplicate_info THEN
RAISE_APPLICATION_ERROR (
num=> -20107,
msg=> 'Duplicate
department number!');
END insert_dept_emp_info;
/
SCHEMA trigger
The following example
creates a BEFORE statement trigger on the sample schema. When a user
connected as hr attempts to drop a database object, Oracle fires the
trigger before dropping the object:
SQL> CREATE OR REPLACE
TRIGGER drop_trigger
BEFORE DROP ON scott.SCHEMA
BEGIN
INSERT INTO audit_table
VALUES
(USER || ' dropped its
table on: '
|| sysdate);
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR (
num => -20000,
msg => 'Cannot drop
object');
END;
/
|
