déclencheurs bases de données...

C. Bonnet / R. Chapuis C. Bonnet / R. Chapuis Cours PL/SQL d’après cours ORACLE - OAI

Déclencheurs Bases de Données TRIGGER

Déclencheurs Bases de Données TRIGGER

C. Bonnet / R. ChapuisC. Bonnet / R. Chapuis Cours PL/SQL d’après cours ORACLE - OAI / 2

GénéralitésGénéralités

• Un déclencheur est un traitement (sous forme de bloc PL/SQL) qui s’exécute automatiquement en réponse à un événement.

• Deux types :– Déclencheur base de données– Déclencheur d’application

• Un déclencheur est un traitement (sous forme de bloc PL/SQL) qui s’exécute automatiquement en réponse à un événement.

• Deux types :– Déclencheur base de données– Déclencheur d’application


ApplicationApplication

SQL> INSERT INTO EMP2 . . .;

SQL> INSERT INTO EMP2 . . .;

EMP tableEMP tableEMPNO

7838

7698

7369

7788

ENAME

KING

BLAKE

SMITH

SCOTT

JOB

PRESIDENT

MANAGER

CLERK

ANALYST

SAL

5000

2850

800

3000

CHECK_SAL triggerCHECK_SAL trigger

Déclencheur Base de Données Exemple

Déclencheur Base de Données Exemple


Création d’un déclencheurCréation d’un déclencheur

• Synchronisation : BEFORE ou AFTER ou INSTEAD OF

• Événement : INSERT ou UPDATE ou DELETE

• Nom Table : On table• Type: Ligne(Row) ou ordre SQL• Condition restrictive : Clause When • Traitement associé : Bloc PL/SQL

• Synchronisation : BEFORE ou AFTER ou INSTEAD OF

• Événement : INSERT ou UPDATE ou DELETE

• Nom Table : On table• Type: Ligne(Row) ou ordre SQL• Condition restrictive : Clause When • Traitement associé : Bloc PL/SQL


Élément d’un déclencheurÉlément d’un déclencheur

Synchronisation :

• BEFORE : Le traitement est exécutéavant l’ordre LMD qui l’a déclenché.

• AFTER : Le traitement est exécuté après l’ordre LMD qui l’a déclenché.

Synchronisation :Synchronisation :

• BEFORE : Le traitement est exécutéavant l’ordre LMD qui l’a déclenché.

• AFTER : Le traitement est exécuté après l’ordre LMD qui l’a déclenché.



Synchronisation :

• INSTEAD OF: Le traitement est exécutéen lieu et place de l’exécution de l’ordre LMD qui l’a déclenché.

• Utilisée pour faire des mises à jour "à travers" des VUES.

Synchronisation :Synchronisation :

• INSTEAD OF: Le traitement est exécutéen lieu et place de l’exécution de l’ordre LMD qui l’a déclenché.

• Utilisée pour faire des mises à jour "à travers" des VUES.



Événement: • Indique quel ordre SQL déclenche le

traitement :– INSERT– UPDATE – DELETE– Toute combinaison de ces ordres

ÉÉvvéénementnement: : • Indique quel ordre SQL déclenche le

traitement :– INSERT– UPDATE – DELETE– Toute combinaison de ces ordres


Élément d’un déclencheurÉlément d’un déclencheurType :Combien de fois le traitement doit s'exécuter suite à l'événement qui l'a déclenché ?• ORDRE : Le traitement est exécuté une

seule fois. – Option par défaut.

• Ligne (Row) : Le traitement est exécutépour chaque ligne affectée par l'événement.

Type Type ::Combien de fois le traitement doit Combien de fois le traitement doit s'exs'exéécuter suite cuter suite àà l'l'éévvéénement qui l'a nement qui l'a ddééclenchclenchéé ??• ORDRE : Le traitement est exécuté une

seule fois. – Option par défaut.

• Ligne (Row) : Le traitement est exécutépour chaque ligne affectée par l'événement.



Traitement - corps du déclencheur :

Quelle action à exécuter ?• Le corps du déclencheur est défini sous

forme d'un bloc PL/SQL anonyme.

Traitement Traitement -- corps du dcorps du dééclencheur :clencheur :

Quelle action Quelle action àà exexéécuter ?cuter ?• Le corps du déclencheur est défini sous

forme d'un bloc PL/SQL anonyme.


SQL> UPDATE emp2 SET sal = sal * 1.13 WHERE deptno = 30;

SQL> UPDATE emp2 SET sal = sal * 1.13 WHERE deptno = 30;

Example Example

Déclencheur ordre et ligneDéclencheur ordre et ligne


EMPNOEMPNO

78397839

76987698

77887788

ENAMEENAME

KINGKING

BLAKEBLAKE

SMITHSMITH

DEPTNODEPTNO

3030

3030

3030

BEFORE BEFORE ordreordre

BEFORE BEFORE ligneligneAFTER AFTER ligneligneBEFORE BEFORE ligneligneAFTERAFTER ligneligneBEFORE BEFORE ligneligneAFTER AFTER ligneligne

AFTER AFTER ordreordre

Synchronisation du déclencheurSynchronisation du déclencheur


Création d'un déclencheur ORDRE

Création d'un déclencheur ORDRE

CREATE [OR REPLACE] TRIGGER nom_déclencheurpositionnement événement [OR événement OR ...]ON Nom_table PL/SQL block;

CREATE [OR REPLACE] TRIGGER nom_déclencheurpositionnement événement [OR événement OR ...]ON Nom_table PL/SQL block;


Exemple : Déclencheur BEFOREExemple : Déclencheur BEFORE

SQL> CREATE OR REPLACE TRIGGER secure_emp2 BEFORE INSERT ON emp3 BEGIN4 IF (TO_CHAR (sysdate,'DY') IN ('SAT','SUN'))5 OR (TO_CHAR(sysdate,'HH24')NOT BETWEEN 6 '08' AND '18' 7 THEN RAISE_APPLICATION_ERROR (-20500,8 'Vous ne pouvez utiliser la table EMP

que pendant les heures normales.');10 END IF;11 END;12 /

SQL> CREATE OR REPLACE TRIGGER secure_emp2 BEFORE INSERT ON emp3 BEGIN4 IF (TO_CHAR (sysdate,'DY') IN ('SAT','SUN'))5 OR (TO_CHAR(sysdate,'HH24')NOT BETWEEN 6 '08' AND '18' 7 THEN RAISE_APPLICATION_ERROR (-20500,8 'Vous ne pouvez utiliser la table EMP

que pendant les heures normales.');10 END IF;11 END;12 /


Exemple (suite)Exemple (suite)

SQL> INSERT INTO emp (empno, ename, deptno)

2 VALUES (7777, 'BAUWENS', 40);

INSERT INTO emp (empno, ename, deptno)

*

ERROR at line 1:

ORA-20500: 'Vous ne pouvez utiliser la table EMP que pendant les heures normales.

ORA-06512: at "SCOTT.SECURE_EMP", line 4

ORA-04088: error during execution of trigger

'SCOTT.SECURE_EMP'

SQL> INSERT INTO emp (empno, ename, deptno)

2 VALUES (7777, 'BAUWENS', 40);

INSERT INTO emp (empno, ename, deptno)

*

ERROR at line 1:

ORA-20500: 'Vous ne pouvez utiliser la table EMP que pendant les heures normales.

ORA-06512: at "SCOTT.SECURE_EMP", line 4

ORA-04088: error during execution of trigger

'SCOTT.SECURE_EMP'


Conditions sur l'événementConditions sur l'événementSQL>CREATE OR REPLACE TRIGGER secure_emp2 BEFORE INSERT OR UPDATE OR DELETE ON emp3 BEGIN4 IF (TO_CHAR (sysdate,'DY') IN ('SAT','SUN')) OR5 (TO_CHAR (sysdate, 'HH24') NOT BETWEEN '08' AND '18') THEN6 IF DELETING THEN7 RAISE_APPLICATION_ERROR (-20502,8 'Suppression impossible à cette heure.');9 ELSIF INSERTING THEN10 RAISE_APPLICATION_ERROR (-20500,11 'Création impossible à cette heure.');12 ELSIF UPDATING ('SAL') THEN13 RAISE_APPLICATION_ERROR (-20503,14 'Modification impossible à cette heure.');15 ELSE16 RAISE_APPLICATION_ERROR (-20504,17 'Mises à jour impossibles à cette heure.');18 END IF;19 END IF;20 END;

SQL>CREATE OR REPLACE TRIGGER secure_emp2 BEFORE INSERT OR UPDATE OR DELETE ON emp3 BEGIN4 IF (TO_CHAR (sysdate,'DY') IN ('SAT','SUN')) OR5 (TO_CHAR (sysdate, 'HH24') NOT BETWEEN '08' AND '18') THEN6 IF DELETINGDELETING THEN7 RAISE_APPLICATION_ERROR (-20502,8 'Suppression impossible à cette heure.');9 ELSIF INSERTINGINSERTING THEN10 RAISE_APPLICATION_ERROR (-20500,11 'Création impossible à cette heure.');12 ELSIF UPDATING ('SAL')UPDATING ('SAL') THEN13 RAISE_APPLICATION_ERROR (-20503,14 'Modification impossible à cette heure.');15 ELSE16 RAISE_APPLICATION_ERROR (-20504,17 'Mises à jour impossibles à cette heure.');18 END IF;19 END IF;20 END;


Exemple : Déclencheur AFTERExemple : Déclencheur AFTERSQL>CREATE OR REPLACE TRIGGER check_salary_count2 AFTER UPDATE OF sal ON emp3 DECLARE4 v_salary_changes NUMBER;5 v_max_changes NUMBER;6 BEGIN7 SELECT upd, max_upd8 INTO v_salary_changes, v_max_changes9 FROM audit_table10 WHERE user_name = user11 AND table_name = 'EMP'12 AND column_name = 'SAL';13 IF v_salary_changes > v_max_changes THEN14 RAISE_APPLICATION_ERROR (-20501,15 'Respectez le maximum : '||16 TO_CHAR (v_max_changes) ||17 ' admissible pour le salaire');18 END IF;19 END;

SQL>CREATE OR REPLACE TRIGGER check_salary_count2 AFTER UPDATE OF sal ON emp3 DECLARE4 v_salary_changes NUMBER;5 v_max_changes NUMBER;6 BEGIN7 SELECT upd, max_upd8 INTO v_salary_changes, v_max_changes9 FROM audit_table10 WHERE user_name = user11 AND table_name = 'EMP'12 AND column_name = 'SAL';13 IF v_salary_changes > v_max_changes THEN14 RAISE_APPLICATION_ERROR (-20501,15 'Respectez le maximum : '||16 TO_CHAR (v_max_changes) ||17 ' admissible pour le salaire');18 END IF;19 END;


USER_NAME

SCOTT

SCOTT

JONES

TABLENAME

EMP

EMP

EMP

COLUMN_NAME

SAL

INS

1

0

UPD

1

1

0

DEL

1

0

MAX_INS

5

5

MAX_UPD

5

5

0

MAX_DEL

5

0

......

Utilisation d'une table d'AUDITUtilisation d'une table d'AUDIT


Création d'un déclencheur LIGNE

Création d'un déclencheur LIGNE

CREATE [OR REPLACE] TRIGGER nom_déclencheurpositionnement événement [OR événement OR ...]ON nom_table [REFERENCING OLD AS old | NEW AS new]FOR EACH ROW[WHEN condition]PL/SQL block;

CREATE [OR REPLACE] TRIGGER nom_déclencheurpositionnement événement [OR événement OR ...]ON nom_table [REFERENCING OLD AS old | NEW AS new]FOR EACH ROW[WHEN condition]PL/SQL block;

OLD : avant exécution de l'ordre LMD

NEW : après exécution de l'ordre LMD

OLD : avant exécution de l'ordre LMD

NEW : après exécution de l'ordre LMD


SQL>CREATE OR REPLACE TRIGGER audit_emp2 AFTER DELETE OR INSERT OR UPDATE ON emp3 FOR EACH ROW4 BEGIN5 IF DELETING THEN6 UPDATE audit_table SET del = del + 17 WHERE user_name = user AND table_name = 'EMP'8 AND column_name IS NULL;9 ELSIF INSERTING THEN

10 UPDATE audit_table SET ins = ins + 111 WHERE user_name = user AND table_name = 'EMP'12 AND column_name IS NULL;13 ELSIF UPDATING ('SAL') THEN14 UPDATE audit_table SET upd = upd + 115 WHERE user_name = user AND table_name = 'EMP'16 AND column_name = 'SAL';17 ELSE /* The data manipulation operation is a general UPDATE. */18 UPDATE audit_table SET upd = upd + 119 WHERE user_name = user AND table_name = 'EMP'20 AND column_name IS NULL;21 END IF;22 END;

SQL>CREATE OR REPLACE TRIGGER audit_emp2 AFTER DELETE OR INSERT OR UPDATE ON emp3 FOR EACH ROW4 BEGIN5 IF DELETING THEN6 UPDATE audit_table SET del = del + 17 WHERE user_name = user AND table_name = 'EMP'8 AND column_name IS NULL;9 ELSIF INSERTING THEN

10 UPDATE audit_table SET ins = ins + 111 WHERE user_name = user AND table_name = 'EMP'12 AND column_name IS NULL;13 ELSIF UPDATING ('SAL') THEN14 UPDATE audit_table SET upd = upd + 115 WHERE user_name = user AND table_name = 'EMP'16 AND column_name = 'SAL';17 ELSE /* The data manipulation operation is a general UPDATE. */18 UPDATE audit_table SET upd = upd + 119 WHERE user_name = user AND table_name = 'EMP'20 AND column_name IS NULL;21 END IF;22 END;

Déclencheur AFTER : ExempleDéclencheur AFTER : Exemple


SQL>CREATE OR REPLACE TRIGGER audit_emp_values2 AFTER DELETE OR INSERT OR UPDATE ON emp3 FOR EACH ROW4 BEGIN5 INSERT INTO audit_emp_values (user_name,6 timestamp, id, old_last_name, new_last_name,7 old_title, new_title, old_salary, new_salary)8 VALUES (USER, SYSDATE, :old.empno, :old.ename,9 :new.ename, :old.job, :new.job,

10 :old.sal, :new.sal);11 END;12 /

SQL>CREATE OR REPLACE TRIGGER audit_emp_values2 AFTER DELETE OR INSERT OR UPDATE ON emp3 FOR EACH ROW4 BEGIN5 INSERT INTO audit_emp_values (user_name,6 timestamp, id, old_last_name, new_last_name,7 old_title, new_title, old_salary, new_salary)8 VALUES (USER, SYSDATE, :old.empno, :old.ename,9 :new.ename, :old.job, :new.job,

10 :old.sal, :new.sal);11 END;12 /

Utilisation des valeurs avant et après

Utilisation des valeurs avant et après


USER_NAME

EGRAVINA

NGREENBE

TIMESTAMP

12-NOV-97

10-DEC-97

ID

7950

7844

OLD_LAST_NAME

NULL

MAGEE

NEW_LAST_NAME

HUTTON

TURNER

OLD_TITLE

NULL

CLERK

NEW_TITLE

ANALYST

SALESMAN

NEW_SALARY

3500

1100

...OLD_SALARY

NULL

1100

Utilisation de la table Audit_Emp_Values

Utilisation de la table Audit_Emp_Values


SQL>CREATE OR REPLACE TRIGGER derive_commission_pct2 BEFORE INSERT OR UPDATE OF sal ON emp3 FOR EACH ROW4 WHEN (new.job = 'SALESMAN')5 BEGIN6 IF INSERTING THEN :new.comm := 0;7 ELSE /* UPDATE of salary */8 IF :old.comm IS NULL THEN9 :new.comm :=0;

10 ELSE11 :new.comm := :old.comm * (:new.sal/:old.sal);12 END IF;13 END IF;14 END;15 /

SQL>CREATE OR REPLACE TRIGGER derive_commission_pct2 BEFORE INSERT OR UPDATE OF sal ON emp3 FOR EACH ROW4 WHEN (new.job = 'SALESMAN')5 BEGIN6 IF INSERTING THEN :new.comm := 0;7 ELSE /* UPDATE of salary */8 IF :old.comm IS NULL THEN9 :new.comm :=0;

10 ELSE11 :new.comm := :old.comm * (:new.sal/:old.sal);12 END IF;13 END IF;14 END;15 /

Restriction déclencheur LIGNEClause WHEN

Restriction déclencheur LIGNEClause WHEN


Déclencheur ou Procédure Stockée

Déclencheur ou Procédure Stockée

Déclencheur

CREATE TRIGGER

Code source et p-code dans le dictionnaire de la base

Appel implicite

COMMIT, SAVEPOINT, ROLLBACK non autorisés

Procédure

CREATE PROCEDURE

Code source et p-code dans le dictionnaire de la base

Appel explicite

COMMIT, SAVEPOINT, ROLLBACK autorisés


ALTER TRIGGER nom_déclencheur DISABLE | ENABLE;ALTER TRIGGER nom_déclencheur DISABLE | ENABLE;

Gestion des DéclencheursGestion des Déclencheurs

Activation / dActivation / déésactivation d'un seul dsactivation d'un seul dééclencheurclencheur

ALTER TABLE nom_table DISABLE | ENABLE ALL TRIGGERS;ALTER TABLE nom_table DISABLE | ENABLE ALL TRIGGERS;

Activation / dActivation / déésactivation de tous les sactivation de tous les ddééclencheurs associclencheurs associéés s àà une tableune table

ALTER TRIGGER trigger_name COMPILE;ALTER TRIGGER trigger_name COMPILE;

Recompilation Recompilation dd’’un dun dééclencheurclencheur


Suppression d'un DéclencheurSuppression d'un Déclencheur

DROP TRIGGER :DROP TRIGGER :DROP TRIGGER :

DROP TRIGGER nom_déclencheur;DROP TRIGGER nom_déclencheur;


Règles d'utilisation des Déclencheurs Ligne

Règles d'utilisation des Déclencheurs Ligne

• Règle 1: Ne pas modifier les données des colonnes définies comme : primary key, foreign key, ouunique key d’une table liée.

• Règle 2: Ne pas accéder aux données d'une table en mise à jour (« mutating » table).

• Règle 1: Ne pas modifier les données des colonnes définies comme : primary key, foreign key, ouunique key d’une table liée.

• Règle 2: Ne pas accéder aux données d'une table en mise à jour (« mutating » table).


éévvèènementnement

TraitementTraitement SQL> UPDATE dept2 SET deptno = 13 WHERE deptno = 30;

SQL> UPDATE dept2 SET deptno = 13 WHERE deptno = 30;


769876547499

ENAME

BLAKEMARTINALLEN

DEPTNO

303030

IntIntéégritgritéé rrééfféérentiellerentielle DEPT tableDEPT tableDNAME

ACCOUNTINGRESEARCHSALESOPERATIONS

DEPTNO

10203040

ErreurTable liTable liééee AFTER UPDATEAFTER UPDATE

ligneligne

xxxxxxxxxxxxxxxxxxxxxxxxxxxxvvvvvvvvvvvvvvvvvvvvvvvvvvvvxxxxxxxxxxxxxxxxxxxxxxxxxxxxvvvvvvvvvvvvvvvvvvvvvvvvvvvvxxxxxxxxxxxxxxxxxxxxxxxxxxxx

xxxxxxxxxxxxxxxxxxxxxxxxxxxxvvvvvvvvvvvvvvvvvvvvvvvvvvvvxxxxxxxxxxxxxxxxxxxxxxxxxxxxvvvvvvvvvvvvvvvvvvvvvvvvvvvvxxxxxxxxxxxxxxxxxxxxxxxxxxxx

DDééclencheursclencheursen cascadeen cascade

Déclencheur et Tables Liées (1) Déclencheur et Tables Liées (1)


Déclencheur et Tables Liées (2) : Déclencheur sur table DEPT

Déclencheur et Tables Liées (2) : Déclencheur sur table DEPT

SQL>CREATE OR REPLACE TRIGGER cascade_updates2 AFTER UPDATE OF deptno on DEPT3 FOR EACH ROW4 BEGIN5 UPDATE emp6 SET emp.deptno = :new.deptno7 WHERE emp.deptno = :old.deptno;8 END;9 /

SQL>CREATE OR REPLACE TRIGGER cascade_updates2 AFTER UPDATE OF deptno on DEPT3 FOR EACH ROW4 BEGIN5 UPDATE emp6 SET emp.deptno = :new.deptno7 WHERE emp.deptno = :old.deptno;8 END;9 /


Déclencheur et Tables Liées (3) : Action sur table DEPT

Déclencheur et Tables Liées (3) : Action sur table DEPT


*ERROR at line 1:ORA-04091: table DEPT is mutating, trigger/functionmay not see it


*ERROR at line 1:ORA-04091: table DEPT is mutating, trigger/functionmay not see it


éévvèènementnement

SQL> UPDATE emp2 SET sal = 15003 WHERE ename = 'SMITH';



736976987788

ENAME

SMITHBLAKESCOTT

SAL

150028503000

ERREUR

Table en mise Table en mise àà jourjour BEFOREBEFOREUPDATEUPDATEligneligne

CHECK_SALARYCHECK_SALARYtriggertrigger

Consultation de données sur une table en mise à jour (« Mutating »)Consultation de données sur une table en mise à jour (« Mutating »)

JOB

CLERKMANAGERANALYST


Table en mise à jour : exemple - 1Table en mise à jour : exemple - 1

SQL>CREATE OR REPLACE TRIGGER check_salary2 BEFORE INSERT OR UPDATE OF sal, job ON emp3 FOR EACH ROW4 WHEN (new.job <> 'PRESIDENT')5 DECLARE6 v_minsalary emp.sal%TYPE;7 v_maxsalary emp.sal%TYPE;

SQL>CREATE OR REPLACE TRIGGER check_salary2 BEFORE INSERT OR UPDATE OF sal, job ON emp3 FOR EACH ROW4 WHEN (new.job <> 'PRESIDENT')5 DECLARE6 v_minsalary emp.sal%TYPE;7 v_maxsalary emp.sal%TYPE;


Table en mise à jour : exemple - suiteTable en mise à jour : exemple - suite

8 BEGIN9 SELECT MIN(sal), MAX(sal)10 INTO v_minsalary, v_maxsalary11 FROM emp12 WHERE job = :new.job;13 IF :new.sal < v_minsalary OR14 :new.sal > v_maxsalary THEN15 RAISE_APPLICATION_ERROR(-20505,16 'salaire hors normes');17 END IF;18 END;19 /

8 BEGIN9 SELECT MIN(sal), MAX(sal)10 INTO v_minsalary, v_maxsalary11 FROM emp12 WHERE job = :new.job;13 IF :new.sal < v_minsalary OR14 :new.sal > v_maxsalary THEN15 RAISE_APPLICATION_ERROR(-20505,16 'salaire hors normes');17 END IF;18 END;19 /


Table en mise à jour : exemple - suiteTable en mise à jour : exemple - suite


*ERROR at line 2ORA_4091 : Table EMP is mutating, trigger/functionmay not see itORA_06512: at line 4ORA_04088: error during execution of trigger 'check_salary'


*ERROR at line 2ORA_4091 : Table EMP is mutating, trigger/functionmay not see itORA_06512: at line 4ORA_04088: error during execution of trigger 'check_salary'


Utilisation des DéclencheursUtilisation des Déclencheurs

• Sécurité• Audit• Intégrité des données• Intégrité référentielle• Réplication de données• Données dérivées• Génération d'événements

•• SSéécuritcuritéé• Audit• Intégrité des données• Intégrité référentielle• Réplication de données• Données dérivées• Génération d'événements


SQL>CREATE OR REPLACE TRIGGER check_salary2 BEFORE UPDATE OF sal ON emp3 FOR EACH ROW4 WHEN (new.sal < old.sal) OR 5 (new.sal > old.sal * 1.1)6 BEGIN7 RAISE_APPLICATION_ERROR (-20508,8 'Il ne faut pas diminuer le salaire ni

l'augmenter de plus de 10%.');10 END;11 /

SQL>CREATE OR REPLACE TRIGGER check_salary2 BEFORE UPDATE OF sal ON emp3 FOR EACH ROW4 WHEN (new.sal < old.sal) OR 5 (new.sal > old.sal * 1.1)6 BEGIN7 RAISE_APPLICATION_ERROR (-20508,8 'Il ne faut pas diminuer le salaire ni

l'augmenter de plus de 10%.');10 END;11 /

Exemple : Intégrité des donnéesExemple : Intégrité des données


SQL>CREATE OR REPLACE TRIGGER cascade_updates2 AFTER UPDATE OF deptno ON dept3 FOR EACH ROW4 BEGIN5 UPDATE emp6 SET emp.deptno = :new.deptno7 WHERE emp.deptno = :old.deptno;8 END;9 /

SQL>CREATE OR REPLACE TRIGGER cascade_updates2 AFTER UPDATE OF deptno ON dept3 FOR EACH ROW4 BEGIN5 UPDATE emp6 SET emp.deptno = :new.deptno7 WHERE emp.deptno = :old.deptno;8 END;9 /

Exemple : Intégrité référentielleExemple : Intégrité référentielle


SQL>CREATE OR REPLACE TRIGGER emp_replica2 BEFORE INSERT OR UPDATE ON emp3 FOR EACH ROW4 BEGIN /*Only proceed if user init. data operation, 5 NOT the casc. trigger.*/6 IF INSERTING THEN7 IF :new.flag IS NULL THEN8 INSERT INTO emp@sf VALUES (:new.empno,9 :new.ename,...,'B');10 :new.flag = 'A';11 END IF;12 ELSE /* Updating. */13 IF :new.flag = :old.flag THEN14 UPDATE emp@sf SET ename = :new.ename, ...,15 FLAG = :new.flag16 WHERE empno = :new.empno;17 END IF;18 IF :old.flag = 'A' THEN :new.flag := 'B';19 ELSE :new.flag := 'A'; 20 END IF;21 END IF;22 END;23 /

SQL>CREATE OR REPLACE TRIGGER emp_replica2 BEFORE INSERT OR UPDATE ON emp3 FOR EACH ROW4 BEGIN /*Only proceed if user init. data operation, 5 NOT the casc. trigger.*/6 IF INSERTING THEN7 IF :new.flag IS NULL THEN8 INSERT INTO emp@sf VALUES (:new.empno,9 :new.ename,...,'B');10 :new.flag = 'A';11 END IF;12 ELSE /* Updating. */13 IF :new.flag = :old.flag THEN14 UPDATE emp@sf SET ename = :new.ename, ...,15 FLAG = :new.flag16 WHERE empno = :new.empno;17 END IF;18 IF :old.flag = 'A' THEN :new.flag := 'B';19 ELSE :new.flag := 'A'; 20 END IF;21 END IF;22 END;23 /

Exemple : Réplication de donnéesExemple : Réplication de données


SQL>CREATE OR REPLACE PROCEDURE increment_salary2 (v_id IN dept.deptno%TYPE,3 v_salary IN dept.total_salary%TYPE)4 IS5 BEGIN6 UPDATE dept7 SET total_sal = NVL (total_sal,0)+ v_salary8 WHERE deptno = v_id;9 END increment_salary;10 /

SQL>CREATE OR REPLACE PROCEDURE increment_salary2 (v_id IN dept.deptno%TYPE,3 v_salary IN dept.total_salary%TYPE)4 IS5 BEGIN6 UPDATE dept7 SET total_sal = NVL (total_sal,0)+ v_salary8 WHERE deptno = v_id;9 END increment_salary;10 /

SQL>CREATE OR REPLACE TRIGGER compute_salary2 AFTER INSERT OR UPDATE OF sal OR DELETE ON emp3 FOR EACH ROW4 BEGIN5 IF DELETING THEN increment_salary(:old.deptno, -1 * :old.sal);6 ELSIF UPDATING THEN increment_salary(:new.deptno, 7 :new.sal-:old.sal);8 ELSE /*inserting*/ increment_salary(:new.deptno, :new.sal);9 END IF;10 END;11 /

SQL>CREATE OR REPLACE TRIGGER compute_salary2 AFTER INSERT OR UPDATE OF sal OR DELETE ON emp3 FOR EACH ROW4 BEGIN5 IF DELETING THEN increment_salary(:old.deptno, -1 * :old.sal);6 ELSIF UPDATING THEN increment_salary(:new.deptno, 7 :new.sal-:old.sal);8 ELSE /*inserting*/ increment_salary(:new.deptno, :new.sal);9 END IF;10 END;11 /

Exemple : données dérivéesExemple : données dérivées


SQL>CREATE OR REPLACE TRIGGER notify_reorder_rep2 AFTER UPDATE OF amount_in_stock, reorder_point ON inventory3 FOR EACH ROW4 WHEN new.amount_in_stock <= new.reorder_point5 DECLARE6 v_descrip product.descrip%TYPE;7 v_msg_text VARCHAR2(2000);8 BEGIN9 SELECT descrip INTO v_descrip

10 FROM PRODUCT WHERE prodid = :new.product_id;11 v_msg_text := 'It has come to my personal attention that, 12 due to recent '13 CHR(10) || 'transactions, our inventory for product # '|| 14 TO_CHAR(:new.product_id)||'--'15 ||:new.product_name ||'-- has fallen' || CHR(10) || CHR(10) || 16 'Yours,' ||CHR(10) ||user || '.';17 dbms_mail.send ('Inventory', user,null,null,'Low 18 Inventory',null,v_msg_text);19 END;20 /

SQL>CREATE OR REPLACE TRIGGER notify_reorder_rep2 AFTER UPDATE OF amount_in_stock, reorder_point ON inventory3 FOR EACH ROW4 WHEN new.amount_in_stock <= new.reorder_point5 DECLARE6 v_descrip product.descrip%TYPE;7 v_msg_text VARCHAR2(2000);8 BEGIN9 SELECT descrip INTO v_descrip

10 FROM PRODUCT WHERE prodid = :new.product_id;11 v_msg_text := 'It has come to my personal attention that, 12 due to recent '13 CHR(10) || 'transactions, our inventory for product # '|| 14 TO_CHAR(:new.product_id)||'--'15 ||:new.product_name ||'-- has fallen' || CHR(10) || CHR(10) || 16 'Yours,' ||CHR(10) ||user || '.';17 dbms_mail.send ('Inventory', user,null,null,'Low 18 Inventory',null,v_msg_text);19 END;20 /

Exemple : Génération d'événementsExemple : Génération d'événements


ProcProcééduredure PackagePackage DDééclencheurclencheur

xxxxxxxxxxxxxxxxxxvvvvvvvvvvvvvvvvvvxxxxxxxxxxxxxxxxxxvvvvvvvvvvvvvvvvvvxxxxxxxxxxxxxxxxxxvvvvvvvvvvvvvvvvvvxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxvvvvvvvvvvvvvvvvvvxxxxxxxxxxxxxxxxxxvvvvvvvvvvvvvvvvvvxxxxxxxxxxxxxxxxxxvvvvvvvvvvvvvvvvvvxxxxxxxxxxxxxxxxxx

xxxxxxxxxxxxxxxxxxvvvvvvvvvvvvvvvvvvxxxxxxxxxxxxxxxxxxvvvvvvvvvvvvvvvvvvxxxxxxxxxxxxxxxxxxvvvvvvvvvvvvvvvvvvxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxvvvvvvvvvvvvvvvvvvxxxxxxxxxxxxxxxxxxvvvvvvvvvvvvvvvvvvxxxxxxxxxxxxxxxxxxvvvvvvvvvvvvvvvvvvxxxxxxxxxxxxxxxxxx

ProcProcéédure Adure Addééclarationclaration

ProcProcéédure Bdure Bddééfinitionfinition

RésuméRésumé

ProcProcéédure Adure Addééfinitionfinition

variablevariablelocalelocale

déclencheurs bases de données...

Documents