iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Problem Solver

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |

 

Online Oracle Training for beginners and advanced - The most comprehensive Oracle tutorial

The authors do not guarantee or take any responsibility for the accuracy, or completeness of the information.

DBAs - Fundamentals

 

Lesson 01 | Lesson 02 | Lesson 03 | Lesson 04 | Lesson 05 | Lesson 06 | Lesson 07 | Lesson 08 | Lesson 09 | Lesson 10 | Lesson 11 | Lesson 12 | Lesson 13 | Lesson 14 | Lesson 15 | Lesson 16 | Lesson 17 | Lesson 18 | Lesson 19 | Lesson 20 | Lesson 21 | Lesson 22 | Lesson 23 | Lesson 24 | Lesson 25 | Lesson 26 |

 

Lesson 22

"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?