Home
Create TABLE
Primay key (1 column)
Primary key (two-column)
Simple Loops
While Loops
For Loops
Triggers
Procedures
Functions
| |
Oracle: PL/SQL - Triggers
|
Oracle Trigger Structure |
|
CREATE [ OR REPLACE ] TRIGGER Trigger_Name
|
|
Trigger_Timing Trigger_Event
|
|
[
Referencing clause ]
|
|
[
FOR EACH ROW ]
|
|
[WHEN (Condition)]
|
|
[
DECLARE sequence of declarative statements ]
|
|
BEGIN sequence of statements
|
|
[
EXCEPTION exception handling statements ]
|
|
END;
|
POINTS TO REMEMBER
Example
ROW TRIGGER
Trigger to ensure that a salary does not exceed by 20 % .
|
CODE
CREATE OR REPLACE TRIGGER emp_salary
BEFORE UPDATE OF Sal
ON EMP
FOR EACH ROW
WHEN (new.sal>1.2*old.sal)
DECLARE
sal_exception_20 EXCEPTION;
Prompt_msg varchar(100);
BEGIN
RAISE sal_exception_20;
EXCEPTION
WHEN sal_exception_20 THEN
Prompt_msg:='Increase in salary should not exceed 20 %.';
Raise_Application_Error(-20001, Prompt_msg);
END;
/ |
--TESTING
SET SERVEROUTPUT ON;
--INCREASING SALARY BY 30 %
-- This should raise exception
UPDATE EMP
SET SAL=SAL*1.3
WHERE EMPNO=7499;
--INCREASING SALARY BY 7 %
UPDATE EMP
SET SAL=SAL*0.7
WHERE EMPNO=7499;
Rollback;
|
| |
|
