|
"If
something comes to life in others because of you, then you
have made an approach to immortality."
-Norman
Cousins (1912-1990)
|
Examples
of creating different triggers...
An
example of creating a DML trigger:
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. The trigger write a message into "audit_table" that
at such day a user
inserted or
update such column.
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;
/
An
example of creating a DDL Trigger:
This example
shows that a dba is monitoring or checking when and who creates or
drops an object in the database.
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;
/
An
example of creating a Database Event Trigger:
This example
shows that a dba is monitoring who and when shuts down a database.
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;
/
An
example of creating an "INSTEAD OF" trigger:
This example
shows that how you can use an "INSTEAD OF" triger.
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.
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;
/
An
example of creating a SCHEMA trigger:
Creating a
SCHEMA Trigger: Example
The
following example creates a BEFORE statement trigger on the sample
schema hr. When a user connected as hr attempts to drop a database
object, Oracle fires the trigger before dropping the object:
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;
/
|