14 Triggers

database triggers ■

objectives : –explain

what is database trigger –list the advantages of triggers –list types of triggers –state & explain the syntax for creating a trigger –construct database triggers ■

what is a database trigger? –database

trigger is a pl/sql program unit that is associated with a specific table –triggers are similar to subprograms, in that they are named pl/sql blocks with declarative, executable and exception-handling sections

■

the difference between a trigger and a subprogram is –a

subprogram is executed explicitly from another block via a procedure call –a subprogram can also accept arguments –a trigger is executed implicitly whenever the triggering event happens –a trigger doesn’t accept arguments

■

firing of a trigger : –the

act of executing a trigger is known as firing the trigger –the triggering event is a dml (insert, update or delete) operation on a database table

you can define a trigger on a table, which are based on dml statements (insert, delete and update) ■you can define trigger on ddl events (create, drop and alter) ■you can also define trigger on database events (servererror, logon, logoff, startup and shutdown) ■

advantages of triggers ■triggers

behave uniformly with respect to all the users and applications ■triggers can be used to –enforcing

complex business rules operations –maintain replicate tables/signaling other tables –computing values –security –audit

use of triggers ■enforcing

complex business rules

–triggers

can be used to implement complex integrity constraints not possible through declarative constraints in table creation

■audit

operations

–auditing

information in a table by recording the changes made and who made them –log events transparently based on dml operations ■

maintain replicate tables/signaling other tables –triggers

can be used for automatically signaling other programs that action need to take place when changes are made to the table

■

computing values –a

trigger can be used to derive column values automatically

■

security –triggers

can be used to implement complex security authorisations

types of triggers ■

triggers are categorised on –level –time

of firing –triggering event

types of triggers

■

trigger types on the basis of level : –statement

trigger –row trigger

statement trigger ■the

trigger action is performed once for the operation that fires the trigger

■used –the –the

when the trigger action is independent of data provided by the operation that fires the trigger number of affected rows

■example

: maintaining the log of operation on a particular

table

row trigger ■the

trigger action is performed repeatedly once for each row that is affected by the operation

■used –the –the

when the trigger action is dependent on data provided by the operation that fires the trigger number of affected rows

■example

: maintaining consolidated balance with each transaction

triggering time

the triggers can specify the time of trigger action ■possible option for the time of a trigger action are ■

–before

the triggering event –after the triggering event

before trigger ■the

trigger action is performed before the operation, that fires the trigger, is executed

■used

when the execution of operation depends on trigger action

■example

: in a bank account, before debiting an account, checking whether the current balance is greater than the amount to be debited

after trigger ■the

trigger action is performed after the operation, that fires the trigger, is executed ■this trigger is used when triggering action depends on the execution of operation ■example

: in a bank account, updating balance after debiting or crediting an account

triggering events ■triggering

events are the dml operations ■triggering events divide triggers into following types –delete

triggers –insert triggers –update triggers ■when

these operations are performed on a table, the associated triggers are fired

trigger fundamentals ■

trigger components : –a

database trigger has got following components ■a

triggering event trigger constraint ■a trigger action ■a

creating a trigger

■create

trigger command is used

create [or replace] trigger [ before | after ] [ delete | [or] insert | [or] update [ of < column1>, [ …. ] on [ for each row [when ]] begin …. -- pl/sql code …. end;

■trigger_name

:

–trigger_name ■triggering

is the name of the trigger

event :

–triggering

event specifies operation that fires the trigger

■table_name –table_name

:

■trigger_body

is the table for which the trigger is defined

–trigger_body

:

is the main code for the trigger

■trigger_condition

:

–the

trigger_condition in the when clause, if present, is evaluated first –the body of the trigger is executed only when this condition evaluates to true

total number of triggers trigger category

values

statement

insert, update, delete

timing

before or after

level

statement or row

■there

is no restriction on number of triggers you can have on a table

restrictions on triggers –a

trigger can not issue any transaction control statements

commit, rollback or savepoint –any

procedures or functions that are called by the trigger body cannot issue any transaction control statements

–the

trigger body can not declare any long or long raw variables

–there

are restrictions on which tables a trigger body may access, depending on the type of trigger and the constraints on the tables

managing triggers

replacing triggers ■dropping triggers ■enabling and disabling triggers ■

replacing triggers

you can not explicitly alter a trigger ■you must recreate it with a new trigger definition ■create trigger statement is used with replace option ■

dropping triggers

you drop a trigger when you do not want to use it any more ■syntax : ■

–drop

■

trigger trigger_name;

this removes the trigger from the data dictionary

enabling and disabling triggers ■a

trigger is enabled by default when it is created

■when

the trigger is disabled, it still exists in the data

dictionary but is never fired ■to

disable / re-enable a trigger, use alter trigger command

■syntax

:

alter trigger trigger_name disable / enable ; ■

■

all triggers for a particular table can be enabled or disabled using alter table command as well syntax : alter table table_name [disable / enable] all triggers;

order of trigger firing ■order

in firing the triggers 1. execute the before statement-level trigger, if present 2. for each row affected by the statement ■execute

the before row-level trigger, if present ■execute the statement/operation itself ■execute the after row-level trigger, if present

3. execute the after statement-level trigger, if present

the order in which the trigger of same type are fired is not defined ■if the order is important for you, combine all of the operations into one trigger ■

using :old and :new in row-level triggers ■inside

the row trigger, you can access the row that is currently being processed

■this

is accomplished through two pseudo records

–:old

–:new ■the

type of both pseudo records is

–triggering_table%

statement :old

rowtype

:new

insert

undefined all the fields statement is complete

update

original values for the row before the update

delete

original values before the row is deleted

values that will be inserted when the are null new values that will be updated when statement is complete undefined – all the fields are null

:old & :new - points to remember ■can

not be assigned as entire records

■only ■can

the individual fields within them may be assigned

not be passed to procedures or functions as arguments

■when

the statement is actually executed, whatever values are in the :new will be used

using :new ■

example:

create trigger generateid before insert on emp for each row begin select emp_sequence.nextval into :new.empno from dual; end generateid;

the when clause ■in

a row level trigger, the trigger body will be executed only for those rows that meet the condition specified ■the syntax for when clause is when ■where is a boolean expression that will be

evaluated for each row ■

■

the :new and :old records can be referenced inside condition as well, but the colon is not used there example : a trigger that will only be executed during data manipulation if the salary for the employee is greater than 5000

create trigger checksal before insert or update on emp for each row when (new.sal>5000) begin -- trigger body goes here end checksal;

using trigger predicates: inserting, updating and deleting ■a trigger can be defined on any combination of dml statements ■there has to be a mechanism for the trigger body to determine the statement by which the current trigger is fired ■

this can be found out by using trigger predicates –inserting –updating –deleting

■

they are basically boolean functions create or replace trigger logchanges before insert or update or delete on logtable for each row declare v_changetype char(1); begin if inserting then v_changetype := `i’;

-- some code here elsif updating then v_changetype := `u’; -- some code here else v_changetype := `d’; -- some code here end if; … … end logchanges;

■create

example 1

a trigger for emp table to check the salary range, during data manipulation. salary range is specified in salmast table and the salary range check is not applicable for ‘president’. ■salmast table columns –job –minsal –maxsal

solution: create or replace trigger check_salary before insert or update of sal, job on emp for each row when (new.job !=‘president’) declare v_minsal number; v_maxsal number; begin select minsal, maxsal into v_minsal, v_maxsal from salmast where job = :new.job; if ( :new.sal < v_minsal or :new.sal > v_maxsal ) then raise_application_error ( -20200, ‘salary out of range’); end if; end check_salary;

example 2

■

■

empstats table stores the summary based on employee information. create a trigger that maintains empstats table. empstats table : –deptno –totalemp

–totalsal

create or replace trigger updateempstats after insert or delete or update on emp declare cursor c_stats is select deptno, count(*) total_emp, sum(sal) total_sal from emp group by deptno; begin for emprec in c_stats loop update emp_stats set total_emp = emprec.total_emp, total_sal = emprec.total_sal where deptno = emprec.deptno; if sql%notfound then insert into emp_stats values ( emprec.deptno, emprec.total_emp, emprec.total_sal); end if; end loop; end updateempstats;

example 3

create a trigger that audits an emp table. the operations table is used for auditing. ■audit table : ■

–operation –ontable –date –user

additional examples 1) create or replace trigger test_trigger1 before insert on test_trigger begin if( trim(' ' from to_char(sysdate,'day'))= 'saturday') then raise_application_error(-20001,'no operations on week-end'); end if; end; 2)

create or replace trigger test_trigger2 before update on test_trigger for each row begin if (:new.age >100) then raise_application_error(-20002,'--age not within limits'); end if; end; 3) create or replace trigger mutating_trigger after update on student for each row v_age number(3); begin select age into v_age from student where name ='amit'; end; 4) create or replace trigger dd_trigger after alter on database begin dbms_output.put_line('ddl trigger'); end; ddl events 1) create 2) alter 3) drop 5) create or replace trigger system_triger after logon on database begin insert into test_trigger values (400,null,null);end; database events 1) after servererror 2) after logon 3) before logoff 4) after startup 5) before shutdown create or replace trigger upd_trigger instead of update on emp_dept_view for each row

begin if :old.ename <> :new.ename then update emp set ename = :new.ename where ename = :old.ename; elsif :old.sal<> :new.sal then update emp set sal = :new.sal where sal = :old.sal; elsif :old.deptno <> :new.deptno then update emp set deptno = :new.deptno where deptno = :old.deptno; elsif :old.loc <> :new.loc then update dept set loc = :new.loc where loc = :old.loc; end if; end;