|
"A doctor can
bury his mistakes but an architect can only advise his clients
to plant vines." - Frank Lloyd Wright (1868-1959) |
Database Triggers
Introduction
The Oracle database
triggers are triggers that will occur when an Oracle event happens
either by the DML statements such as UPDATE, DELETE , INSERT, etc; the
DDL statements such as DROP, CREATE, etc; the Database events such as
SHUTDOWN, STARTUP, etc; or events in a schema such as dropping a table
in an specific schema.
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;
/
|
"Forgive your
enemies, but never forget their names." - John F. Kennedy
(1917-1963) |
Questions:
Q: What are the Oracle
database triggers?
Q: What is an Oracle event
trigger?
Q: What is a DML trigger?
Q: What is a DDL trigger?
Q: What is a database event
trigger?
Q: What is an INSTEAD OF
trigger?
Q: What is a schema
trigger?
|