Oracle:Database Triggers

Main Page >> Oracle and SQL >> Workbook >> Advanced Features >> Database Triggers

An Introduction to Database Triggers

Database triggers are programs that are executed after a specific event has happened on a target table, such as inserting a row. They are commonly used for:

• Audit purposes
• Security
• Complex Integrity Checks that cannot be handled by constraints

If you refer back to the employee table EMP and salary grade table SALGRADE you can see that there exists a complex relationship, one not based on Foreign Keys. At present it is possible to insert a row in the EMP table whose salary is not covered by the SALGRADE table. To enforce such a check you can create a trigger and store it within the database.

The following PL/SQL script enforces such a check:

CREATE TRIGGER EMP_AFTERCHANGE
after INSERT
or    UPDATE of SAL
on    EMP
for each row
DECLARE
  salarygrade NUMBER;
BEGIN
   select grade
   into salarygrade
   from SALGRADE
   where LOSAL <= :new.sal
   and   HISAL >= :new.sal;
EXCEPTION
   when NO_DATA_FOUND then
       raise_application_error(-20001, 'Salary given is not included in the salary grades');
   when OTHERS then 
       raise_application_error(-20000, 'Error has occured in EMP_AFTERCHANGE trigger');
END;
/

This PL/SQL trigger declares a numeric variable to store a specific salary grade that the employee earns. If this query returns no values then the EXCEPTION code is fired. In this block a check is to see if the pre-defined error NO_DATA_FOUND has occurred or the catch all error has occurred. If so then the pre-defined raise_application_error procedure is called with a suitable error code and message. This procedure rollbacks any of the changes in the current transaction. The error codes must be in the range -20000 and 20999.

If you now attempt to enter a row or update a row in the EMP table that does not have a corresponding salary grade Oracle will notify you of the error.

INSERT INTO EMP
VALUES (1111,'TEST’,’JOB',NULL,'21-AUG-94',10000,NULL,10, NULL);

Whenever you create a trigger or procedure you may get compilation errors. To give full details of the most recent Trigger or Procedure compilation errors enter the SQL command:

SHOW ERRORS;

Next Step

Database Procedures