|
Examples
of creating different triggers...
|
More Resources by
Google: |
|
|
|
|
Provided By: John Kazerooni
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;
/
Good
Luck!
|