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 II

 

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 27 | Lesson 28 | Lesson 29 |

 

Lesson 13

“You can tell a lot about a fellow's character by his way of eating jellybeans.” Ronald Reagan (1911 - ), quoted in Observer, March 29 1981

 

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;

/