מדריך PL-SQL – טריגרים (Triggers)

Trigger הינה פרוצדורה הנקראת ע"י מנוע ה-Oracle בכל פעם שמתבצע משפט INSERT, UPDATE או DELETE על טבלה מסויימת.

ייתרונות הטריגרים:

• קל לתחזק טבלאות שיעתוק (רפליקציה)
• אכיפת שלמות המידע בבסיס הנתונים
• עריכת שינויי המידע הנערכים
• העלאה אוטומטית (Autoincrement) של שדה
• ועוד…

תחביר

Create or replace trigger <triggername>
[before/after] [insert/update/delete] 
on <tablename> [for each satement/ for each row]
[when <condition>]

ישנם שני סוגים של טריגרים:

• before – טריגר המתבצע לפני פקודת ה-INSERT/UPDATE/DELETE
• after – טריגר המתבצע לאחר שינוי/הכנסת המידע

ניתן למחוק טריגר ע"י המשפט:

Drop Trigger <triggername> ; 

דוגמאות

-- A database trigger that allows changes to employee table only
-- during the business hours(i.e. from 8 a.m to 5.00 p.m.)
-- from monday to saturday. There is no restriction on viewing 
-- data from the table

CREATE OR REPLACE TRIGGER Time_Check
    BEFORE INSERT OR UPDATE OR DELETE
    ON EMP
BEGIN
    IF TO_NUMBER(TO_CHAR(SYSDATE,'hh24')) < 10 
     OR TO_NUMBER(TO_CHAR(SYSDATE,'hh24')) >= 17 
     OR TO_CHAR(SYSDATE,'DAY') = 'SAT' 
     OR TO_CHAR(SYSDATE,'DAY') = 'SAT'
        THEN
        RAISE_APPLICATION_ERROR (-20004,'YOU CAN ACCESS ONLY BETWEEN 10 AM TO 5 PM ON MONDAY TO FRIDAY ONLY.');
    END IF;
END; 

-- YOU HAVE 2 TABLES WITH THE SAME STRUCTURE. IF U DELETE 
-- A RECORD FROM ONE TABLE, IT WILL BE INSERTED IN 2ND TABLE


Create or replace trigger backup after 
delete on emp for each row
begin
     insert into emp2 values (:old.ename,:old.job,:old.sal);
end;

-- To STICK IN SAL FIELD BY TRIGGER MEANS WHEN U ENTER GREATER 
-- THAN 5000, THEN THIS TRIGGER IS EXECUTED

Create or replace trigger check before
insert on emp for each row when (New.sal > 5000);
     begin
             raise_application_error(-20000, 'your number is greater than 5000');
     end;

-- NO CHANGES CAN BE DONE ON A PARTICULAR TABLE ON SUNDAY 
-- AND SATURDAY

Create or replace trigger change before on
emp for each row when (to_char(sysdate,'dy') in ('SAT','SUN'))
    begin
         raise_application_error(-200001, 'u cannot enter data in saturnday and sunday');
    end;

-- IF U ENTER IN EMP TABLE ENAME FIELD'S DATA IN ANY CASE 
-- IT WILL BE INSERTED IN CAPITAL LETTERS'S ONLY

Create or replace trigger cap before 
insert on emp for each row
begin
     :New.ename = upper(:New.ename);
end;

-- A TRIGGER WHICH WILL NOT ALLOW U TO ENTER DUPLICATE VALUES
-- IN FIELD EMPNO IN EMP TABLE 

Create or replace trigger dubb 
before insert on emp for each row
Declare
     cursor c1 is select * from emp;
     x emp%rowtype;
     begin
             open c1;
            loop
                fetch c1 into x;
                if :New.empno = x.empno then
                 dbms_output.put_line('you entered duplicated no');
                elseif :New.empno is null then
                 dbms_output.put_line('you empno is null');
                end if;
                exit when c1%notfound;
         end loop;
            close c1;
    end;