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;
