Overblog Suivre ce blog
Administration Créer mon blog
25 mai 2011 3 25 /05 /mai /2011 23:54

Oracle DML Trigger  Before INSERT or DELETE or UPDATE.

Un trigger est une procédure qui est géré automatiquement par la base de données.
Les déclencheurs (TRIGGERS) sont par exemple utiles pour loguer des opérations DML (INSERT, UPDATE, DELETE) d'une table.

Deux types de triggers DML différents :

• les triggers table (STATEMENT) à déclenchement unique.
• les triggers ligne (ROW) à déclenchement sur chaques lignes.

 

Syntax CREATE TRIGGER DML.

 
 CREATE [ OR REPLACE ] TRIGGER [ schema. ]trigger { BEFORE | AFTER | INSTEAD OF } { DELETE | INSERT | UPDATE [ OF column [, column ]... ] } [ OR { DELETE | INSERT | UPDATE [ OF column [, column]... ] } ]... ON { [ schema. ]table } [ FOR EACH ROW ] ON { [ schema. ]SCHEMA } } [ WHEN (condition) ] { pl/sql_block } ; 

 

Exemple TRIGGER Ligne DML BEFORE INSERT or DELETE or UPDATE.

 
 SQL> CREATE OR REPLACE TRIGGER Log_emp 2 BEFORE INSERT OR DELETE OR UPDATE ON emp 3 FOR EACH ROW 4 BEGIN 5 IF INSERTING THEN 6 DBMS_OUTPUT.put_line('Insert on emp ' ||' '|| USER ||' ' ||SYSDATE); 7 ELSIF UPDATING THEN 8 DBMS_OUTPUT.put_line('Update on emp ' ||' '|| USER ||' ' ||SYSDATE); 9 ELSIF DELETING THEN 10 DBMS_OUTPUT.put_line('Delete on emp ' ||' '|| USER ||' ' ||SYSDATE); 11 END IF; 12 END; 

 

Liens en rapport avec l'article et qui sont susceptibles de vous intéresser
• Activer Désactiver Supprimer Trigger Oracle
• Types TRIGGERS Table et TRIGGERS Ligne ORACLE
Repost 0
Published by Daniel Roesch - dans ORACLE TRIGGERS
commenter cet article
21 mai 2011 6 21 /05 /mai /2011 11:06

Les Types de Triggers Table et Triggers Ligne Oracle

Les déclencheurs (triggers) existent depuis la version 6 d’Oracle.
Les triggers permettent d’exécuter automatiquement des procédures centralisées dans la base de données.
Les triggers se déclenchent sur des actions modifiant les données de la table sur laquelle porte le trigger.
Les triggers se déclenchent lorsqu’un événement précis survient : BEFORE UPDATE, AFTER DELETE, AFTER INSERT, etc. Ils définissent le moment d’exécution du trigger.

Deux types de triggers différents :

• les triggers table (STATEMENT)
• les triggers ligne (ROW).

 

Les différences entre TRIGGER TABLLE et TRIGGER LIGNE.

• Les triggers table sont exécutés une seule fois lorsque des modifications surviennent sur une ou plusieurs lignes de la table. Souvent utilisés pour des opérations de groupe.

- il n’est pas possible d’avoir accès à la valeur ancienne et la valeur nouvelle (OLD et NEW).

Exemple TRIGGER table

 
 CREATE TRIGGER log_in_table AFTER INSERT OR UPDATE ON scott.emp BEGIN INSERT INTO log_table (table_name, date, username, evenement) VALUES ('scott.emp', sysdate, sys_context('USERENV','CURRENT_USER'), 'Insert/Update scott.emp'); END ; 

Ce trigger table enregistre dans une table log_table une trace de la modification de la table Emp. Ce trigger est exécuté qu’une seule fois par modification de la table Emp.



• Les triggers lignes sont exécutés séparément pour chaque ligne modifiée dans la table.

- il est possible d’avoir accès à la valeur ancienne et la valeur nouvelle grâce aux mots clés OLD et NEW.

Exemple TRIGGER ligne

 
 CREATE OR REPLACE TRIGGER log_mute_table AFTER UPDATE OF deptno ON scott.emp FOR EACH ROW WHEN (OLD.deptno < 96 AND NEW.deptno > 95 ) BEGIN INSERT INTO log_mutable (deptno_old, deptno_new, date,sal_name, evenement) VALUES (OLD.deptno, NEW.deptno, sysdate, NEW.ename, ‘Mutation Etranger') ; END ; 

Ce trigger ligne est déclenché lorsque le champ deptno de la table Emp est mise à jour, seulement si l’ancienne valeur de deptno est inferieur 96 et si la nouvelle valeur est supérieur à 95.
Pour chaque déclenchement le trigger va insérer dans la table log_mutable l’ancienne valeur et nouvelle valeur de deptno.
La condition WHEN restreint le déclenchement du trigger (exécuté uniquement si OLD.deptno < 96 AND NEW.deptno > 95).

Il n’est pas permis de consulter ou modifier une table mutante. (Sinon que par OLD et NEW).
Cette restriction préserve le TRIGGER LIGNE de lire des données inconsistantes sur laquelle il porte.

 

Liens en rapport avec l'article et qui sont susceptibles de vous intéresser
• Activer Désactiver Supprimer Trigger Oracle
Repost 0
Published by Daniel Roesch - dans ORACLE TRIGGERS
commenter cet article
18 février 2010 4 18 /02 /février /2010 00:38

 

Activer, Désactiver, Supprimer un Trigger sur Table Oracle

Il peut être utile et même conseillé suivant le contexte de désactiver un Trigger lors d'une opération de maintenance lourde ou d'administration sur une table (PL/SQL, SQL*Loader, mise à jour en masse,etc...).

● Avec ALTER TRIGGER
● Avec ALTER TABLE
● Avec DROP TRIGGER

Activer/Désactiver le Trigger par son nom

SQL> ALTER TRIGGER MonTrigger ENABLE;
SQL> ALTER TRIGGER MonTrigger DISABLE;



Activer/Désactiver tous les Triggers d'une table


SQL> ALTER TABLE MaTable ENABLE ALL TRIGGERS;
SQL> ALTER TABLE MaTable DISABLE ALL TRIGGERS;



Supprimer un Trigger Oracle


SQL> DROP TRIGGER Nom_Trigger;

Voir les tables d'un SCHEMA qui ont des Triggers actifs (Enabled)

 


SQL> SELECT DISTINCT table_owner,
                     table_name,
                     status
     FROM all_triggers
     WHERE owner = 'MonUser'
     AND status = 'ENABLED';



La même requête pour récupérer le SQL à exécuter

SQL> SELECT DISTINCT
            'alter table '||table_owner||'.'||table_name||' DISABLE ALL TRIGGERS;'"SQL"
     FROM all_triggers
     WHERE owner = 'MonUser'
     AND status = 'ENABLED';

Attention pour la réactivation des triggers plus tard, ne réactiver pas ceux qui étaient intentionnellement désactiver!!


Voir les triggers du USER en cours


SQL> SELECT * FROM user_triggers;


 

Liens en rapport avec l'article et qui sont susceptibles de vous intéresser
• Types TRIGGERS Table et TRIGGERS Ligne ORACLE
Repost 0
Published by Daniel Roesch - dans ORACLE TRIGGERS
commenter cet article