2.1.3.2 Database Triggers

Database triggers are a useful way to validate data and compute column values in one place, no matter which application access it.

You can define a trigger on a table to execute custom business logic before or after a row in that table is inserted, updated, or deleted. If the trigger fires before the operation, it can enforce validation rules and compute calculated column values if necessary before the row is saved. You can create and maintain your triggers in App Builder using Object Browser.

For example, the following trigger rounds an employee's salary to the closest multiple of 10 and raises an error if the salary value is greater than 9000. The syntax is easy to understand. It executes before insert or update on the EMP table for each row affected. Notice how it can reference the value of EMP table columns using the special bind variable syntax :NEW.column_name.

CREATE OR REPLACE TRIGGER emp_bef_ins_or_upd
BEFORE INSERT OR UPDATE ON emp FOR EACH ROW
BEGIN
    -- If incoming SAL value exceeds 9000, then raise error
    IF :NEW.sal > 9000 THEN
        raise_application_error(-20001,'Maximum salary is 9000');
    END IF;
    -- Round to nearest multiple of ten. The -1 means
    -- 1 digit to the *left* of the decimal point
    :NEW.sal := ROUND(:NEW.sal, -1);
END;

After creating this trigger on the EMP table, an attempt to set the employee JAMES' salary to 9954 using the following SQL statement results in an error:

UPDATE emp
   SET sal = 9954
 WHERE empno = 7900 /* JAMES */

As expected, this produces the following error and prevents the statement's intended change:

ORA-20001: Maximum salary is 9000

If instead you update JAMES' salary to 954 using a similar UPDATE statement, the update succeeds. However, if you SELECT the salary again, the trigger rounds the value 954 to 950.