[SQL SERVER 2005]créer un trigger pour chaque table de chaque DB

Bonjour,

Je suis d'un nouveau projet qui est de centralisé les logs de différentes applications dans une DB.

J'ai donc créé un nouvelle DB nommée DbLog.
Cette DB est constitué d'une table nommé tbLog qui se compose comme suit :

Code:

---

1 2 3 4 5 6

timestp (PK, timestamp, not null) username (varchar(50), not null) database_name (varchar(50), not null) table_name (varchar(50), not null) old_record (text, null) new_record (text, null)

---

J'ai donc pensé à créer un trigger qui viendrait mettre à jour cette table à chaque fois qu'on fait un insert, update ou delete dans une autre db.
Jusque là, pas de souci (créer un trigger, c'est pas bien dur).

Le truc c'est que, étant un gros fainéant, je n'ai pas envie de m'amuser à faire sur chaque table de chaque DB right clic > new trigger et ensuite copier/coller le code en changeant le nom des DB et table.

Je cherche donc comment créer en une seule opération un trigger sur chaque table de chaque DB. Je présume qu'il doit bien avoir un moyen mais n'ayant jamais eu de formation (j'ai tout appris plus ou moins sur le tas), je patauge.

Quelqu'un pourrait-il m'aiguiller pour trouver la marche à suivre ?

Merci d'avance.

Griftou.

Construisez dynamiquement le script de création du trigger en faisant un select sur information_schema.tables.

Je veux bien que j'ai dit que créer un trigger n'était pas bien dur (avec sql server management studio, y a presque rien à faire) mais là je suis perdu.

Pourriez-vous développer un peu plus ?

Tu comptes le récupérer où "username" ?

Ah merde ! Bien vu !
J'avais déjà virer le champ application à cause de ça...

Mmh... je suppose que je peux le retirer dans la mesure où, dans la plupart des applis (à terme dans toutes), j'ai déjà un champ username pour savoir qui a fait la dernière modification sur le record.

Par extension, j'aurai donc le username dans l'ancien ou le nouveau record selon le cas.

Note par rapport à mon post précédent :

Si je savais comment créer des users sur les db, je pourrais créer un user pour chaque utilisateur de l'application et je pourrais donc récupérer le username dans la requête puisqu'il s'agira du user connecté au server (je ne sais pas encore comment mais je suis persuadé que c'est accessible).

Donc en théorie c'est possible mais dans la pratique, ce n'est pas (encore) possible dans mon cas.

Est-ce que les query qui modifient les données existent déjà ?
Si oui, peux-tu encore les modifier ?

Il serait intéressant qu'on connaisse les libertés que tu as.

Tout d'abord, merci pour la réponse.

Ensuite bin... Oui les queries existent mais il s'agit de requêtes faites par des application .NET.

Le but était de ne pas modifier les applications existantes et aussi de ne pas devoir ajouter du code dédier au logging dans les nouvelles applications.

De tout faire via trigger. En soit, c'est pas dur. After insert, je colle le nouveau record dans la db en mettant les valeurs du record dans le champ "new_record" et "old_record" à null, l'inverse pour delete et pour update, je pense que vous aurez devinez.

Ce n'est pas bien dur de faire le trigger pour une table. Mais le truc c'est que j'aimerais faire tous les triggers d'un coup...

Je sais même pas si c'est possible en fait mais j'imagine que oui...

J'avais pensé à écrire une procédure avec 2 boucles for each imbriquée qui itéreraient pour chaque db et pour chaque table (sauf la db de log). Mais là encore, va falloir que je bouquine un peu ce langage là car ça fait 2 ans que je ne fais plus que du .NET.

Griftou.

Ok.

Par ailleurs si tu envisages un nombre important de records, tu devrais ne pas employer tant de varchar comme clé.

Crée une table "Databases" : id (pk identity) + nom
Crée une table "Table" : id (pk identity) + nom + dbid (fk vers "Databases")

Ta table principale deviendrait :
timestp (PK, timestamp, NOT NULL)
username (varchar(50), NOT NULL)
old_record (text, NULL)
new_record (text, NULL)
table_id (FK, INT, NOT NULL)


Si tu n'as pas envie d'en faire de trop, pense quand même à ce que tu risques de devoir faire plus tard si tes mécanismes sont à l'antipode d'une bonne pratique.

Bin ce n'est pas que je n'ai pas envie d'en faire trop, c'est juste que je n'ai jamais eu aucune formation sur le sujet.

Donc si c'est vraiment mieux niveau performance, j'utiliserai ta structure.

Mais ça ne résoud tjs pas mon problème de création de trigger ^^

Vous trouverez toutes les informations nécessaire à cette fonction dans les articles que j'ai écrit pour SQL Server Magazine ( l'an passé.
On y parle de différentes méthodes de tracabilité d'évolution des données, y compris avec les noms des utilisateurs et même la MAC ADDRESS du PC client !

L'un de mes articles est d'ailleurs en ligne ici :
http://blog.developpez.com/sqlpro/p8...n-des-donnees/

On y créé automatiquement les triggers pour les tables concernées à l'aide de triggers DDL.

A +

J'étais en congé fin de la semaine dernière.

Je vais de ce pas lire votre article.

Un tout grand merci !

Bonjour,

Je suis en train de tester et de comprendre le fonctionnement de la procédure que vous décrivez dans votre article.

J'en suis au point 2.2 et j'obtiens une erreur lors de l'exécution du script. La voici :

Citation:

---

An explicit value for the identity column in table 'S_TRK.T_TRACKED_DATATYPES_TDT' can only be specified when a column list is used and IDENTITY_INSERT is ON.

---

Peut-être est-elle due à une erreur de ma part car j'ai du faire quelques légères modifications. J'entends par là, par exemple, enlever les type date et time qui n'existe pas (plus?) sous sql server 2005. Aussi, quelques détails de syntaxe ont du changer car j'ai du par exemple englober dans des paranthèses les lignes après le mot clé VALUES sans quoi j'obtenais une erreur de syntaxe.

Bref, toujours est-il que je ne comprends pas l'erreur en question. On dirait qu'il essaie d'ajouter le nom des datatypes dans le champ TDT_ID au lieu du champ TDT_DATATYPE. L'id est pourtant bien défini comme identité avec un auto incrément. Je sais que quand je fais mes insert, j'ignore également ce type de champ et tout se passe très bien. Donc quid de cette erreur ?

Griftou.

P.S. : Pour info, voici le script après les modifications d'adaptations à ma version de sql server

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

--===========================================================================-- -- 2 : alimentation de la liste des tables et des types de données historisés --===========================================================================--   -- voici comment ou alimente la table des tables à suivre : INSERT INTO S_TRK.T_TRACKED_TABLES_TBL (TBL_SCHEMA, TBL_NAME) SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' -- on évitera d'auditer les tables d'audit ! AND TABLE_SCHEMA + '.' + TABLE_NAME NOT IN ('S_TRK.T_TRACKED_TABLES_TBL', 'S_TRK.T_TRACKED_DATACHANGES_TKC', 'S_TRK.T_TRACKED_DATATYPES_TDT'); -- vous pouvez exclure certaines autres tables de l'historisation -- AND TABLE_NAME NOT IN (...) -- AND TABLE_SCHEMA NOT IN (...)   -- on évitera de spécifier les types BLOBS ou des types complexes (XML, UDT, types RO...). INSERT INTO S_TRK.T_TRACKED_DATATYPES_TDT VALUES (('char'), ('varchar'), ('nchar'), ('nvarchar'), ('decimal'), ('numeric'), ('float'), ('real'), ('int'), ('smallint'), ('bigint'), -- types sépcifiques SQL Server : ('datetime'), ('smalldatetime'), ('datetimeoffset'), ('money'), ('smallmoney'), ('tinyint'), ('bit')); GO

---

Bon, j'ai pigé l'erreur (suis un gros naze en fait ^^).

Les paranthèses autour de tout ce qu'il y a après VALUES, c'est mal.
Mais pourquoi est-ce que je reçois une erreur de syntaxe alors... C'est quoi la syntaxe correct pour ajouter plusieurs lignes ?

J'ai été voir dans l'aide de sql server et si on en croit ce qui est écrit, ce n'est possible qu'avec une subquery...

Donc je fais quoi moi ?

Griftou.

P.S. : En attendant, j'ai fait ajouter moi-même les données dans la table mais bon, ça pue quoi... J'veux comprendre pourquoi ça foire.

EDIT : Une recherche sur google m'a permis de voir qu'il fallait virer VALUES et mettre un select devant chaque bloc de () et de mettre union all entre les lignes a joutées. C'est encore microsoft qui a eu envie de ne pas faire comme tout le monde ou bien un truc spécifique que j'ignore ou n'ai pas compris ?

J'ai finalement réussi.

Un tout grand merci à SQL PRO et ses articles sans lesquels je n'y serais jamais arrivé.

Il m'a fallu un paquet de temps pour les comprendre et arrivé à les bidouiller pour que ça fasse ce que je voulais mais j'y suis finalement arrivé. Enfin presque. Je voulais à la base que ça créée les triggers non seulement pour chaque table mais aussi pour chaque db.

Finalement, ça le fait dans une db données et je me dis que ce n'est pas plus mal.

Bref, j'suis super fier de moi :mouarf:

Merci encore à tous !

Griftou.