Database Triggers
 

A trigger de nition consists of the following (optional) components:

  • trigger name

create [or replace] trigger <trigger name>

  • trigger time point

before | after

  • triggering event(s)

insert or update [of <column(s)>] or delete on <table>

  • trigger type (optional)

for each row

  • trigger restriction (only for for each row triggers !)

when (<condition>)

  • trigger body

<PL/SQL block>

A trigger is a fragment of code that you tell Oracle to run before or after a table is modified. A trigger has the power to :

  • make sure that a column is filled in with default information
  • make sure that an audit row is inserted into another table
  • after finding that the new information is inconsistent with other stuff in the database, raise an error that will cause the entire transaction to be rolled back

Consider the general_comments table:

create table general_comments (
 comment_id  integer primary key,
 on_what_id  integer not null,
 on_which_table  varchar(50),
 user_id   not null references users,
 comment_date  date not null,
 ip_address  varchar(50) not null,
 modified_date date not null,
 content   clob,
 -- is the content in HTML or plain text (the default)
 html_p   char(1) default 'f' check(html_p in ('t','f')),
 approved_p  char(1) default 't' check(approved_p in ('t','f'))
);

Users and administrators are both able to edit comments. We want to make sure that we know when a comment was last modified so that we can offer the administrator a "recently modified comments page". Rather than painstakingly go through all of our Web scripts that insert or update comments, we can specify an invariant in Oracle that "after every time someone touches the general_comments table, make sure that the modified_date column is set equal to the current date-time." Here's the trigger definition:

create trigger general_comments_modified
before insert or update on general_comments
for each row
begin
 :new.modified_date := sysdate;
end;
/
show errors

We're using the PL/SQL programming language. In this case, it is a simple begin-end block that sets the :new value of modified_date to the result of calling the sysdate function. When using SQL*Plus, you have to provide a / character to get the program to evaluate a trigger or PL/SQL function definition. You then have to say "show errors" if you want SQL*Plus to print out what went wrong. Unless you expect to write perfect code all the time, it can be convenient to leave these SQL*Plus incantations in your .sql files.

The canonical trigger example is the stuffing of an audit table.

create table queries (
 query_id integer primary key,
 query_name varchar(100) not null,
 query_owner not null references users,
 definition_time date not null,
 -- if this is non-null, we just forget about all the query_columns
 -- stuff; the user has hand edited the SQL
 query_sql varchar(4000)
);

create table queries_audit (
 query_id integer not null,
 audit_time date not null,
 query_sql varchar(4000)
);

Note first that queries_audit has no primary key. If we were to make query_id the primary key, we'd only be able to store one history item per query, which is not our intent.
How to keep this table filled? We could do it by making sure that every Web script that might update the query_sql column inserts a row in queries_audit when appropriate. But how to enforce this after we've handed off our code to other programmers? Much better to let the RDBMS enforce the auditing:

create or replace trigger queries_audit_sql
before update on queries
for each row
when (old.query_sql is not null and (new.query_sql is null or old.query_sql <> new.query_sql))
begin
  insert into queries_audit (query_id, audit_time, query_sql)
  values
  (:old.query_id, sysdate, :old.query_sql);
end;

The structure of a row-level trigger is the following:

CREATE OR REPLACE TRIGGER ***trigger name***
***when*** ON ***which table***
FOR EACH ROW
***conditions for firing***
begin
  ***stuff to do***
end;

Let's go back and look at our trigger:

  • It is named queries_audit_sql; this is really of no consequence so long as it doesn't conflict with the names of other triggers.
  • It will be run before update, i.e., only when someone is executing an SQL UPDATE statement.
  • It will be run only when someone is updating the table queries.
  • It will be run only when the old value of query_sql is not null; we don't want to fill our audit table with NULLs.
  • It will be run only when the new value of query_sql is different from the old value; we don't want to fill our audit table with rows because someone happens to be updating another column in queries. Note that SQL's three-valued logic forces us to put in an extra test for new.query_sql is null because old.query_sql <> new.query_sql will not evaluate to true when new.query_sql is NULL (a user wiping out the custom SQL altogether; a very important case to audit).

Other Trigger Examples are given below:
Trigger1.sql -- Suppose we have to maintain the following integrity constraint: \The salary of an employee di erent from the president cannot be decreased and must also not be increased more than 10%. Furthermore, depending on the job title, each salary must lie within a certain salary range. We assume a table SALGRADE that stores the minimum (MINSAL) and maximum (MAXSAL) salary for each job title (JOB). Since the above condition can be checked for each employee individually,we de ne the following row trigger:
set echo off

prompt "Example trigger trig1.sql, page 46 Oracle/SQL Tutorial"
prompt
prompt "Creating additional table SALS containing salary ranges..."

set echo on

DROP TABLE SALS;
CREATE TABLE SALS
        (JOB VARCHAR2(9) primary key,
         MINSAL NUMBER(7,2),
         MAXSAL NUMBER(7,2)
        );

INSERT INTO SALS VALUES ('CLERK', 800, 1300);
INSERT INTO SALS VALUES ('ANALYST', 3000, 3500);
INSERT INTO SALS VALUES ('SALESMAN', 1250, 1600);
INSERT INTO SALS VALUES ('MANAGER', 2450, 2975);
INSERT INTO SALS VALUES ('PRESIDENT', 5000, 5500);

create or replace trigger check_salary_EMP
after insert or update of SAL, JOB on EMP
for each row
when (new.JOB != 'PRESIDENT')
declare
 minsal number;
 maxsal number;
begin
 -- retrieve minimum and maximum salary for JOB
 select MINSAL, MAXSAL into minsal, maxsal from SALS
 where JOB = :new.JOB;
 -- If the new salary has been decreased or does not lie
 -- within the salary range raise an exception
 if :new.SAL < minsal or :new.SAL > maxsal then
  raise_application_error(-20225, 'Salary range exceeded');
 elsif :new.SAL < :old.SAL then
  raise_application_error(-20230, 'Salary has been decreased');
 elsif :new.SAL > 1.1*:old.SAL then
  raise_application_error(-20235, 'More than 10% salary increase');
 end if;
end;
/
We use an after trigger because the inserted or updated row is not changed within the PL/SQL block (e.g., in case of a constraint violation, it would be possible to restore the old attributevalues). Note that also modi cations on the table SALGRADE can cause a constraint violation. In order to maintain the complete condition we de ne the following trigger on the table SALGRADE. In case of a violation by an update modi cation, however, we do not raise an exception, but restore the old attribute values.

set echo off

prompt "Example trigger trig2.sql, page 47 Oracle/SQL Tutorial"
prompt
set echo on

create or replace trigger check_salary_SALS
before update or delete on SALS
for each row
when ( new.MINSAL > old.MINSAL  or
       new.MAXSAL <  old.MAXSAL or
       new.MAXSAL is null)
-- only restricting a salary range can cause a constraint violation
declare
 job_emps  number;
begin
 if deleting then -- Does there still exist an employee having the deleted job
  select count(*)  into job_emps  from EMP
  where JOB =  :old.JOB;
  if job_emps != 0  then
  raise_application_error(-20240,' There still exist employees with the job ' ||  :old.JOB);
    end if ;
 end if ;
 if updating then
 -- Are there employees whose salary does not lie within the
        --  modified salary range ?
  select count(*)  into job_emps  from EMP
  where JOB =  :new.JOB  and SAL not between :new.MINSAL and :new.MAXSAL;
  if job_emps != 0  then -- restore old salary ranges
   :new.MINSAL :=  :old.MINSAL;
   :new.MAXSAL :=  :old.MAXSAL;
  end if ;
 end if ;
end;
/
In this case a before trigger must be used to restore the old attribute values of an updated row. Suppose we furthermore have a column BUDGET in our table DEPT that is used to store the budget available for each department. Assume the integrity constraint requires that the total of all salaries in a department must not exceed the department's budget. Critical operations on the relation EMP are insertions into EMP and updates on the attributes SAL or DEPTNO.

set echo on

ALTER TABLE DEPT
       ADD BUDGET NUMBER(8,2);

UPDATE DEPT set BUDGET = 10000 where DEPTNO = 10;
UPDATE DEPT set BUDGET = 15000 where DEPTNO = 20;
UPDATE DEPT set BUDGET = 10000 where DEPTNO = 30;
UPDATE DEPT set BUDGET = 5000 where DEPTNO = 40;

create or replace trigger check_budget_EMP
after insert or update of SAL, DEPTNO  on EMP
declare
 cursor DEPT_CUR is
     select DEPTNO, BUDGET from DEPT;
 DNO   DEPT.DEPTNO%TYPE;
 ALLSAL   DEPT.BUDGET%TYPE;
 DEPT_SAL  number;
begin
  open DEPT_CUR;
  loop
   fetch DEPT_CUR  into DNO, ALLSAL;
   exit when DEPT_CUR%NOTFOUND;
 select sum(SAL)  into DEPT_SAL  from EMP
 where DEPTNO = DNO;
 if DEPT_SAL > ALLSAL then
  raise_application_error(-20325, 'Total of salaries in the department '||       to_char(DNO) || ' exceeds budget');
   end if;
  end loop;
  close DEPT_CUR;
end;
/
In this case we use a statement trigger on the relation EMP because we have to apply an aggregate function on the salary of all employees that work in a particular department. For the relation DEPT, we also have to de ne a trigger which, however, can be formulated as a row trigger.