|
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;
/ |
