Oracle 9: Trigger pour audit trail

Version imprimable

17/01/2006, 15h45
ChrisD

Oracle 9: Trigger pour audit trail

Bonjour,

J'aimerais créer un trigger qui lors de l'update ou l'insert d'un record dans une table "Info" va insérer une ligne dans une table audit trail pour chaque champ modifié.

En fait via une application VB6 je vais mettre à jour un record via un update de recordset ADO.

Ex:
rst.fields(0).value = "Bonjour"
rst.fields(1).value = "Salut"
rst.fields(2).value = "Au revoir"

rst.update

Je voudrais au moment de l'update qu'un trigger soit déclenché. Ce trigger passerait en revue tous les champs de la table "Info", vérifierait si il y a eu une modification de valeur du champ et qui dans l'affirmative insérerait une ligne dans une table audittrail pour signaler le changement de valeur.
Mon problème est que je ne vois pas comment passer tous les champs de la table en revue sans devoir spécifier 1 à 1 et nommément les champs. :?:

Il me faudrait une syntaxe du style

for each field in record
'test si valeur à changer
next

Merci d'avance :D

Christophe
18/01/2006, 03h56
plabrevo

La question est interessante, mais je ne vois pas de solution.

Je pense que la seule solution est de generer les triggers avec un outils pour eviter de coder a la main tous les tests necessaires.

Sur les conseils avisés :?: :wink: d'un collègue j'ai essayé ça mais ça ne fonctionne pas, je reçois le message "PLS-00049: bad bind variable 'OLDREC.M_COLUMNNAME'" sur le IF.

En fait je récupère via un curseur le noms des champs de la table, je boucle sur le curseur, mets le nom du champ dans une variable et essaie d'accéder au champ en question sur mon record courant via l'utilisation de la variable...

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
 
 
CREATE OR REPLACE TRIGGER DEV.AUDITTRAILBONDS
BEFORE UPDATE ON DEV.BONDS 
REFERENCING NEW AS NEWREC OLD AS OLDREC
FOR EACH ROW
 
DECLARE
m_SeqAuditTrail NUMBER;
m_ColumnName VARCHAR(50);
 
CURSOR p_BondsColumns IS
	SELECT COLUMN_NAME 
	FROM ALL_TAB_COLUMNS 
	WHERE OWNER='DEV' AND TABLE_NAME = 'BONDS';
 
BEGIN
 
	FOR CurrentColumn IN p_BondsColumns LOOP
 
		m_ColumnName := CurrentColumn.COLUMN_NAME;
 
		IF :OLDREC.m_ColumnName <> :NEWREC.m_ColumnName THEN
			SELECT SEQ_AUDITTRAIL.NEXTVAL INTO m_SeqAuditTrail FROM DUAL;
 
			INSERT INTO DEV.AUDITTRAIL (AUDITTRAIL_ID, USER_ID, AUDITTRAIL_TABLE, 
			   AUDITTRAIL_FIELD, AUDITTRAIL_OLDVALUE, AUDITTRAIL_NEWVALUE, 
			   AUDITTRAIL_DATETIME, AUDITTRAIL_PK_VALUE, AUDITTRAIL_LASTMODIFICATION, 
			   AUDITTRAIL_RECSTATUS, AUDITTRAIL_USERMODIF) 
			VALUES (m_SeqAuditTrail, 0, 'BONDS',
				    m_ColumnName, :OLDREC.m_ColumnName, :NEWREC.m_ColumnName,
				    sysdate, 'BONDS_ID = ' || :OLDREC.m_ColumnName, sysdate,
				    'ACT', 0);							
		END IF;
 
	END LOOP;
END;

18/01/2006, 13h46
bouyao

Bonjour,
:tagcode:
18/01/2006, 13h49
ChrisD

Citation:

Envoyé par bouyao

Bonjour,
:tagcode:

Voilà c'est fait :D
18/01/2006, 13h56
bouyao

Dans 9i, Oracle à introduit une nouvelle Fonctionnalité FGA (Fine-Grained Auditing) qui est disponible dans le package DBMS_FGA
18/01/2006, 14h13
ChrisD

Citation:

Envoyé par bouyao

Dans 9i, Oracle à introduit une nouvelle Fonctionnalité FGA (Fine-Grained Auditing) qui est disponible dans le package DBMS_FGA

Je ne suis pas sûr que ça résoud mon problème car

1° On doit nommé chaque champ sur lequel porte la policy ajoutée.
2° Si j'ai bien lu ça logge le changement dans une table bien prédéfinie 'FGA_LOG$'
18/01/2006, 14h28
bouyao

le problème de base avec l'audit habituel : il suit seulement qui a executé quelle commande, pas specialement qui l'a fait. Par exemple, il montre que Jaouad à sélectionner certains enregistrements de la table DVP, mais non pas avec des enregistrements particuliers. Si on veut connaître la valeur modifiée, on peut placer des triggers DML sur les tables et capturer les valeurs dans certains tables qu'on a définit. Comme on ne peut pas définir un trigger dans un SELECT, cette option ne marchera pas.

Les fonctionnalités FGA sont disponibles dans le package DBMS_FGA. FGA a été introduit dans Oracle 9i ou il est employé seulement pour la commande SELECT. Dans 10g, il s'applique à toutes les commandes DML.

En utilisant FGA, on peut enregistrer les activités des commandes SELECT, INSERT, UPDATE et DELETE dans le suivi d'audit (non pas dans la table AUD$). Dans ce suivi d'audit, on peut trouver non seulement l'information qui fait quoi, mais beaucoup plus, comme la commande SQL exacte entrer par l'utilisateur, le SCN, la valeur du bind variable (si elle est utilisée) et d'autres choses.

L'un des meilleurs aspects du FGA, c'est qu'il peut être appliqué d'une manière sélective. Pour des activités spécifiques. Par exemple, on veut accomplir un audit quand quelqu'un sélectionne la colonne SAL, et la valeur de SAL est 1500. Cet enregistrement sélectif réduit la quantité des informations d'audit qui sont générés.

Une autre caractéristique très utile du FGA qu'il peut être mener pour exécuter automatiquement une procédure définie par l'utilisateur. Cette capacité peut être exploitée dans plusieurs formes.