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.

No comments:

Post a Comment