Discussion :

[ypelissier]

Bonjour,
Dans un trigger de mise à jour (Update), j'aimerais connaitre la requête qui a fait que ce trigger se déclenche.

1 - Est-ce possible (je pense que oui) ?
2 - Comment fais-je ?

Précisions : Je suis sur SQLLocalDB et j'ai un processus de synchronisation des données avec une base SQL Server 2014 que j'ai développé car la réplication ne fonctionne pas avec SQLLocalDB car celui-ci n'accepte pas de communications extérieures qui ne seraient pas de son initiative.
Du coup j'ai fait ma synnchro (ordre de tables et tout bien comme il faut) mais une table à la particularité de refuser toute modification excepté quelques champs mais lors de cet échange, il faut qu'elle accepte tout sans broncher.

Merci d'avance pour l'aide apportée

[SQLpro]

Bonjour,
Dans un trigger de mise à jour (Update), j'aimerais connaitre la requête qui a fait que ce trigger se déclenche.

1 - Est-ce possible (je pense que oui) ?

Il faut que vous commenciez votre déclencheur par :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
DECLARE @T TABLE (EVENT_TYPE sysname,
                  PARAMS     INT,
				  EVENT_INFO NVARCHAR(max));
INSERT INTO @T				    
EXEC ('DBCC INPUTBUFFER(@@SPID)'); 
 
Dès lors le contenu de la colonne EVENT_INFO de la variable table @T contient en principe la requête précédente dans la session en cours.

A +

[aieeeuuuuu]

Bonjour

N'avez-vous pas envisagé de résoudre votre problématique par le biais des privilèges, en utilisant un utilisateur spécifique pour votre synchronisation ?

[ypelissier]

Effectivement, dans mes recherches j'avais croisé cette ligne de code

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

DBCC INPUTBUFFER(@@SPID)

sans vraiment comprendre que c'était une ligne de code...

J'étais pourtant si proche...

Merci beaucoup, toujours aussi rapide

[ypelissier]

Bonjour

N'avez-vous pas envisagé de résoudre votre problématique par le biais des privilèges, en utilisant un utilisateur spécifique pour votre synchronisation ?

Nos messages se sont croisés...

Non, je n'y ai pas pensé en fait c'est tout simplement parce que je ne connais absolument pas cet aspect là car je suis développeur à la base, donc utilisateur de la base de données et non administrateur d'elle, mais sur ce projet j'ai du tout faire de A à Z, dont la synchro que je ne pouvais pas faire par réplication habituel.

Comment me conseilleriez-vous de faire en utilisant la notion de privilège ?

[aieeeuuuuu]

Quel est la mécanisme actuel qui empeche la mise jour de certaines colonnes de votre table ?

Le principe pourrait être le suivant :
Vous n'accordez le privilège UPDATE que sur les colonnes voulues pour vos utilisateurs habituel, et sur toute la table pour votre utilisateur de synchronisation.

[ypelissier]

Bonjour et merci pour cette réponse sur lequel je vais me documenter un petit peu...
Néanmoins, aujourd'hui, j'utilise le trigger INSTEAD OF UPDATE pour intercepter l'UPDATE et ne faire que ce qui est autorisé à être fait (les règles de mise à jour sont donc codées dedans). Par contre, lors de la synchronisation, j'ai des données qui peuvent avoir été modifiées depuis d'autres sources mais qui doivent être prises telles quelles sans traitement d'analyse d'autorisation. D'où l'idée de récupérer la requête ayant lancé l'INSTEAD OF UPDATE afin de savoir si c'est celle de synchronisation ou non. Si ça l'est, alors l'UPDATE sera exécuté sans contrôle des entrées.

Du coup, une question qui découle directement de l'utilisation des privilèges et de l'utilisateur pour la synchronisation :
Sachant que c'est l'application qui donne l'ordre à LocalDB de lancer la synchronisation, cela signifie qu'il me faille changer de connexion pour utiliser l'utilisateur de synchronisation... Pour le moment j'utilise la connexion windows par défaut, je n'ai pas crée d'utilisateur particulier. Est-ce grave docteur ?

Encore une autre question à la noix, l'application établi une connexion avec LocalDB dès son ouverture sans jamais la fermer car j'utilise toujours la même mais dois-je l'ouvrir à chacune de mes requêtes ou groupe de requêtes et lorsque je redonne la main à l'utilisateur, faudrait-il la fermer ?

Bon ben voilà, merci encore pour vos éclaircissements

[aieeeuuuuu]

En effet, si c'est le même utilisateur qui fait les UPDATE et la synchro, c'est plus compliqué.

Vous pouvez néanmoins passer par une vue :
Privilèges de mise à jour sur les colonnes voulues uniquement sur la table.
création d'une vue sur la table (même propriétaire que la table), avec privilèges de mise à jour sur toutes les colonnes. vous utilisez alors la vue au lieu de la table pour la synchro.

[ypelissier]

Ah oui, on peut faire ça ??? Je suis surpris mais bon si vous le dites, je vous fait confiance

Par contre, puis-je faire le contraire, c'est-à-dire utiliser la vue pour les colonnes restreintes que je veux pouvoir laisser disponible pour la mise à jour par l'application utilisatrice de la base de données et la table pour toutes les colonnes ?

La raison de la demande de cette inversion est simple, le module de synchronisation est autonome et prend la liste des tables pour construire la requête de synchronisation dynamiquement. Comme ça pas besoin de gérer si une nouvelle table est créer, il n'y a juste qu'à déclarer son ordre de synchronisation. Sans cet ordre de synchronisation, la table n'est pas synchronisé.

[aieeeuuuuu]

Par contre, puis-je faire le contraire, c'est-à-dire utiliser la vue pour les colonnes restreintes que je veux pouvoir laisser disponible pour la mise à jour par l'application utilisatrice de la base de données et la table pour toutes les colonnes ?

Oui, tout à fait, j'allais même vous le suggérer car il est préférable que votre application s'appuie sur des vues plutôt que directement sur les tables.
Vous pouvez même renommer la table, et créer la vue portant l'ancien nom de la table. Ainsi, pas besoin de modifier le code existant de votre application.

[ypelissier]

Oui, je connais la théorie de dissociation des tables (contenant des informations) et des vues (présentant des informations) mais je ne l'ai pas appliquée car mon application n'appelle que sur des procédures stockées retournant des données (ou non pour la synchro).
C'est un peu comme des vues sauf qu'elles sont paramétrables contrairement aux vues (ou alors je n'ai pas trouvé comment faire).

De plus, chaque procédure stockée retournant des données est basée sur une fonction (tout autant paramétrable) retournant également des données afin de pouvoir les utiliser dans d'autres requêtes. Peut-être me faudrait-il mettre une couche de vue à ce niveau là, entre les fonctions et les tables ? Qu'en pensez-vous ?

C'est un petit peu de gymnastique cérébrale (et je vous fait grâce de la structure de l'application) mais côté application l'appel en est plus simplifié par l'appel de la procédure stockée avec ses paramètres.

La suppression est tout simplement interdite (vive les triggers), la vie des données étant gérée par un bit présent dans toutes les tables.

P.S. : En fait si, il y a une seule vue qui rassemble une quinzaine de tables et c'est la principale raison de l'application. Une insertion ou une mise à jour de cette vue doit être contrôlée et correctement rangée dans les bonnes tables de façon transparente de la structure de la base de données. D'ailleurs, ces actions (insertion ou la mise à jour de cette vue) appellent elles-même une procédure stockée pour être réalisée.

[aieeeuuuuu]

Attention toutefois aux fonctions : elle peuvent être la cause de problème de performance, qui plus est masqués par le fait que les plan de requête ne tiennent pas compte de leur cout d'exécution. préférez les fonction table en ligne quand cela est possible.

pour en revenir à votre problème, quel est l’intérêt des triggers si vous passez par des procédure stockées ? pourquoi ne pas inclure les règles de gestion des insertions et mises à jour dans les SP ?

Quant à empêcher la suppression par trigger, là aussi, vous pourriez vous contenter de gérer cela grâce aux privilèges.

[ypelissier]

Oui, j'ai pu avoir un soucis de performance en mettant une fonction en place à un moment donnée, j'ai appris à ce moment là... Par contre je vous rassure car toutes les fonction dont je vous ai parlé sont bien des fonctions table en ligne.

L'intérêt de passer par les triggers qui appellent les procédures stockées, il n'y a que dans la vue que c'est fait, les autres agissent directement avec le même type de contrôle simple, date et lieu de modification, utilisateur qui modifie.
La raison de mettre le trigger qui appelle la procédure stockée pour la vue c'est que l'application utilise la procédure stockée et que plutôt que de l'écrire 2 fois la même chose, cela m'a paru plus simple de tout gérer dans une procédure stockée.
D'autre part, certains utilisateurs (responsables connaissant SQL) veulent accéder en direct à la base de données, je protège donc les actions sur des données par contrôle de ce qu'il se passe... Mais j'ai l'impression de comprendre qu'en faite le meilleur moyen de le faire est d'utiliser les privilèges avec un privilège de consultation uniquement pour les responsables

Un grand merci pour cet ouverture sur l'aspect des privilèges, j'ai de quoi lire quelques heures avec ça... Par contre ce ne sera pas pour ce projet car je livre la semaine prochaine, alors je ne veux rien toucher de ce qui pourrait être non maitrisé.

[SQLpro]

Si vous répliquez les données, vous pouvez faire un déclencheur qui n'est pas pris en compte dans le réplication :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
CREATE TRIGGER 
...
NOT FOR REPLICATION

A +

[ypelissier]

Oui, cette instruction je l'avais croisée aussi mais je ne l'ai pas retenue car je ne peux pas faire de réplication avec LocalDB comme expliqué plus haut. Néanmoins ça peut-être utile à d'autres qui pourraient lire ce post