Tables de log : Difficultés avec les triggers

Version imprimable

1 pièce(s) jointe(s)

Bonjour, je souhaite mettre en places certains triggers pour créer une gestion des logs. Je m'explique.

Lors d'un insert sur une table, le trigger doit remplir deux tables avec certaines informations, seulement je ne sais pas comment récupérer certaines données. Il doit premièrement créé une ligne dans une table rassemblant toutes les actions éffectués (INSERT, UPDATE, DELETE ), seulement je cherches à récupérer la dernière requête d'un login donné, j'ai trouvé sur le net ceci :
Code:

1 2 3 4 5 SELECT sqltxt.text FROM sys.dm_exec_connections conn INNER JOIN sys.dm_exec_sessions sson ON conn.session_id = sson.session_id CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) AS sqltxt WHERE sson.login_name = ORIGINAL_LOGIN()
Seulement cette requête me retourne plusieurs résultats !
Pièce jointe 148465

Je cherche ici à récupérer seulement la troisième ligne, les autres sont automatiques et ne m'intéresse pas. Comment ne récupérer QUE la requête exécutée par ce compte de connexion ?

En plus de récupérer certaines info permettant d'identifier l'auteur de la requête, je dois écrire dans une deuxième table plusieurs lignes quant aux actions effectuées à savoir le nom des collones modifiés/insérés, leurs valeur (ancienne et nouvelle si possible, sinon uniquement la valeur après modifications/ajout) et également l'ID du journal de log auquel il est attaché, pour obtenir quelque chose du genre :

Pièce jointe 148471

Donc voila, je cherches à savoir ce qu'il est possible de faire avec les triggers, est-ce que dans mon cas le mieux est de tout mettre dans un seul trigger (qui écrit dans les deux tables) seulement je ne sais pas comment récupérer les informations qui viennent d'être inséré dans 'Relevés', ni comment récupérer l'ID du journal que le trigger créé (pour le lien FK entre Journal et Logs). Ou bien placer un deuxième trigger sur journal pour insérer dans la table Log.

J'ai pas trouvé grand chose sur le net quant à la gestion des logs en base de donnée, évoluant sur une instance SQL Express, j'peux oublier le Profiler :/

Des idées ? :)

J'avoue, j'ai pas tout lu.
Mais en précisant la session avec @@SPID ça va pas mieux ?

Code:

1
2
3
4
5
SELECT sqltxt.text FROM sys.dm_exec_connections conn
INNER JOIN sys.dm_exec_sessions sson 
ON conn.session_id = sson.session_id
CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) AS sqltxt
WHERE sson.login_name = ORIGINAL_LOGIN() and conn.session_id=@@SPID

13/06/2014, 16h17
StripMat

Effectivement, sa fonctionne, merci Donpi !

Malheureusement je vais placer tout sa dans un trigger et lorsque j'éssai de rentrer un insert sur la table cible avec un utilisateur simple, SSMS me retourne une erreur : "L'utilisateur n'est pas autorisé à effectuer cette action", sûrement parceque le trigger va chercher des infos dans les tables systèmes :/

Comment contourner cela ?
13/06/2014, 16h21
mikedavem

Oui il faut avoir le privilège VIEW SERVER STATE pour faire cela qui est un privilège de niveau serveur. En admettant que tu donnes ce droit à tes utilisateurs, je ne pense pas que cela va marcher car la requête la plus récente récupérée ne sera pas ton INSERT, UPDATE ou DELETE mais l'exécution du trigger lui même ... à vérifier

++
13/06/2014, 16h56
StripMat

Vous avez malheureusement raison mikedavem, la requête qui m'est retournée est la requête créant le trigger :/

Une idée pour contourner ceci ? Peut-être récupérer l'avant-dernière requête ? Comment ?

Une autre façon de faire ?

Là j'avoue qu'a moins d'écrire directement depuis l'application métier, j'vois pas comment réaliser sa au niveau base de donnée avec des triggers :(
13/06/2014, 17h10
SQLpro

Citation:

Envoyé par StripMat

Vous avez malheureusement raison mikedavem, la requête qui m'est retournée est la requête créant le trigger :/

Une idée pour contourner ceci ? Peut-être récupérer l'avant-dernière requête ? Comment ?

Vous ne pouvez pas faire comme cela. Si vous voulez la requête intégrale il faut utiliser Change Data Capture.

Citation:

Une autre façon de faire ?

Là j'avoue qu'a moins d'écrire directement depuis l'application métier, j'vois pas comment réaliser sa au niveau base de donnée avec des triggers :(

C'est pas possible, car seule la requête actuelle, celle du trigger sera vue. La requête précédente est peut être conservée, mais sans le contexte de l'utilisateur qui l'a lancée.

A +
13/06/2014, 17h20
StripMat

D'accord, merci pour ces précisions. Mais je n'ai pas bien saisi, qu'est-ce que Change Data Capture ?

Si j'ai bien compris c'est impossible avec des triggers, mais je pourrai en utilisant CDC ?

Désolé mais mon anglais technique n'est pas au point et je n'ai pas compris comment utiliser cet outil sur les docs francophones ... :aie:
15/06/2014, 10h00
SQLpro

Citation:

Envoyé par StripMat

qu'est-ce que Change Data Capture ?

Un outil de traçage du changement des données (INSERT, UPDATE, DELETE) et même de la lecture (SELECT).

Citation:

Si j'ai bien compris c'est impossible avec des triggers, mais je pourrai en utilisant CDC ?

Oui

Citation:

Désolé mais mon anglais technique n'est pas au point et je n'ai pas compris comment utiliser cet outil sur les docs francophones ... :aie:

http://technet.microsoft.com/fr-fr/l...ql.105%29.aspx

A +
16/06/2014, 10h07
StripMat

Merci pour votre réponse !

Mais j'ai oublié de précisé mais je bosse sur une instance EXPRESS de SQL Server, et j'crois bien que CDC n'est pas autorisé pour les instances EXPRESS. Mais en cherchant j'ai trouvé quelque chose qui s'appelle Change Tracking, j'ai testé et sa m'a l'aire assez simple à utiliser et plutôt efficace. J'arrive à récupérer l'action effectuée sur une table donnée, les champs et valeurs modifiées/insérées mais j'ai pas encore trouvé comment récupérer l'utilisateur responsable des actions, j'creuse encore.

Selon vous est-ce une bonne idée d'utiliser Change Tracking, même en phase de production pour controler ce qui se passe en base de donnée ?

J'ai vu quelques posts anglophones qui semblaient déconseiller d'utiliser CDC en phase de production et de privilégier quelque chose comme "SQL Server Audit"

http://msdn.microsoft.com/en-us/library/cc280386.aspx

Moi je cherche juste à analyser qui fait quoi sur mes bdd, j'en suis pas encore à l'optimisation des temps de réponses et etc, donc Change Tracking peut-il me convenir ?

EDIT: Après réflexion, CT n'est peut-être pas assez puissant, je ne peux pas non plus récupérer le datetime des opérations éffectués, je peux savoir ce qui a été fait mais ni quand ni par qui :aie:
16/06/2014, 10h24
SQLpro

Citation:

Envoyé par StripMat

Merci pour votre réponse !

Mais j'ai oublié de précisé mais je bosse sur une instance EXPRESS de SQL Server, et j'crois bien que CDC n'est pas autorisé pour les instances EXPRESS. Mais en cherchant j'ai trouvé quelque chose qui s'appelle Change Tracking, j'ai testé et sa m'a l'aire assez simple à utiliser et plutôt efficace. J'arrive à récupérer l'action effectuée sur une table donnée, les champs et valeurs modifiées/insérées mais j'ai pas encore trouvé comment récupérer l'utilisateur responsable des actions, j'creuse encore.

Selon vous est-ce une bonne idée d'utiliser Change Tracking, même en phase de production pour controler ce qui se passe en base de donnée ?

J'ai vu quelques posts anglophones qui semblaient déconseiller d'utiliser CDC en phase de production et de privilégier quelque chose comme "SQL Server Audit"

http://msdn.microsoft.com/en-us/library/cc280386.aspx

Moi je cherche juste à analyser qui fait quoi sur mes bdd, j'en suis pas encore à l'optimisation des temps de réponses et etc, donc Change Tracking peut-il me convenir ?

Change Tracking comme CDC ne trace que le changement pas l'auteur.
Pour cela il vous faut database audit, mais ce n'est disponible que dans la version Enterprise.

Il n'existe aucun moyen de faire cela de façon simple sans une version supérieure. Par exemple vous pourriez faire cela manuellement avec le Profiler SQL. Mais dispo à partir de version standard !

A +
16/06/2014, 11h06
StripMat

Merci pour ces précisions, je sais maintenant ce qu'il est possible ou non de faire avec une instance EXPRESS, je pense que la seule option qu'il me reste est de confier l'écriture des fichiers logs à la partie métier de notre solution.

Merci encore pour votre aide.
16/06/2014, 13h56
aieeeuuuuu

Citation:

Envoyé par SQLpro

même de la lecture (SELECT)

Tracer les SELECT avec Change Data Capture ? De quelle manière ?
17/06/2014, 12h48
SQLpro

Citation:

Envoyé par aieeeuuuuu

Tracer les SELECT avec Change Data Capture ? De quelle manière ?

non, tu as raison, j'ai dit une connerie !

A +