מדריך 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;
תגובות בפייסבוק