Triggere

1. Triggere Mutasjoner i basen

2. Triggers • Triggers are stored procedures that execute automatically when something (event) happens in the database: • : data modification (INSERT, UPDATE or DELETE) • : schema modification • : system event (user logon/logoff) • Types of triggers • : row-level triggers • : statement-level triggers • : BEFORE and AFTER triggers • : INSTEAD OF triggers (used for views) • : schema triggers • : database-level triggers

3. The optional FOR EACH ROW clause is known as a row-level trigger (i.e., the rule is triggered separately for each tuple). • If FOR EACH ROW clause was left out, the trigger would be known as a statement-level trigger (i.e., the rule would be triggered once for each triggering statement). • The keywords NEW and OLD can only be used with row-level triggers.

4. Rekkefølge Firing order: 1. Before Statement (once only)2. Before Row (once per each affected row)3. The actual DML statement4. After Row (once per each affected row)5. After Statement (once only)

5. Trigger Syntax CREATE OR REPLACE TRIGGER trigger-name BEFORE | AFTER DELETE | INSERT | UPDATE [OF column(s)] ON table-name [FOR EACH ROW [WHEN SQL-predicate] ] {PL/SQL block (SQL statements and PL/SQL extensions)};

6. Trigger Example -- this is an example of an update, after, row trigger CREATE TRIGGER give_bonus AFTER UPDATE OF sales ON salespeople FOR EACH ROW WHEN sales > 8000.00 BEGIN UPDATE commission SET bonus = bonus + 150.00; END;

7. Navnekonvensjon • I FS har vi bare triggere for insert/update/delete. Vi gir dem disse prefixene: • DAR – Delete After Row • DAS – Delete After Statement • DBR – Delete Before Row • DBS – Delete Before Statement • IAR – Insert After Row • IAS – Insert After Statement • IBR – Insert Before Row • IBS – Insert before Statement • UAR – Update After Row • UAS – Update After Statement • UBR – Update Before Row • UBS – Update Before Statement

8. Mutasjoner • ORA-04091: table <tablename> is mutating, trigger/function may not see it • The basic reason for this error is the way Oracle manages a read consistent view of data. The error is encountered when a row-level trigger accesses the same table on which it is based, while executing. The table is said to be mutating. Mutation will not occur if a single record is inserted in the table (using VALUES clause). If bulk insertion is done or data is inserted from another table mutation will occur. • The mutating error is not only encountered during queries, but also for insert, updates and deletes present in the trigger.

9. FS har en mutasjonspakke Bruker en plsqlpakke for hver tabell. Oppretter to lister: newListe og oldListe for gamle og nye rader. Disse listene blir populert i DBR/IBR/UBR-triggerene. I DAS/IAS/UAS-triggerene blir en prosedyre i pakken, for eksempel P_Vedlikehold_Insert, kallet. Disse løper da igjennom de to listene og gjør det triggeren skal gjøre

10. Eksempel på p_vedlikehold --| PROCEDURE P_Vedlikehold_Insert --| Vedlikeholder restriksjoner, kontroller på nye forekomster --| Procedure P_Vedlikehold_Insert Is Begin While newCount# > 0 Loop P_SjekkAktivitetFinnes(newCount#); newCount#:=newCount# - 1; End Loop; End P_Vedlikehold_Insert;

11. Takk for oppmerksomheten! Geir.Solli@usit.uio.no