Types of Triggers

There are two types of triggers ROW type and STATEMENT type. A row trigger is fired for each time the table is affected by the triggering statement. A statement type trigger is fired once on behalf of the triggering statement regardless of the number of rows in the table affected.

Triggers Timing

BEFORE triggers execute the trigger action before the triggering statement. AFTER triggers execute the action after the triggering statement is executed. So with these combinations we can create four types as follows

BEFORE statement trigger
BEFORE row trigger
AFTER statement trigger
AFTER row trigger

CREATE TRIGGER before_trg BEFORE UPDATE ON EMP
CREATE TRIGGER each bef_trg BEFORE UPDATE ON EMP FOR EACH ROW.
EXAMPLE ON TRIGGERS
CREATE TRIGGER check_salary
BEFORE INSERT OR UPDATE OF sal,job ON emp
FOR EACH ROW
WHEN (new.job ! ='PRESIDENT')
DECLARE
minsal NUMBER;
maxsal NUMBER;

BEGIN
SELECT losal,hisal INTO minsal, maxsal FROM sals
WHERE job = :new.job;
IF (:new.sal < minsal OR:new.sal'> maxsal) THEN
raise application_error(-20225,'Salary out of range');
ELSEIF(:new.sal < :old.sal) THEN
raise application _error( 20230,'Negative increase');
ELSEIF(:new.sal > 1.1 * :old.sal) THEN
raise_application_error( 20235, 'Increase exceeds 10%');
END IF;
END;

Domain Name Search