Trigger

A trigger is a sort of procedure that is executed as result of an event generated in the database.

As an example, let's see a trigger that generates a user exception we try to change data in the jobs table in a week day other than wednesday:

create or replace trigger tr_jobs
  before insert or update or delete
  on jobs
begin
  if to_char(sysdate, 'DY') != 'WED' then
    raise_application_error (-20900, 'Today no change allowed');
  end if;
end;

When this trigger is in the database, if we try to insert a new row:

insert into jobs values('XXX', 'Unknown', 0 , 0);

Update:

update jobs set max_salary = 0;

Or delete:

delete jobs where job_id = 'AD_VP';

We always get a SQL error back - if we don't run these statements on Wednesday.

Chapter 19 of Oracle PL/SQL Programming, fifth edition, by Steven Feuerstein is all about triggers.