Les triggers : c'est le mal!

Un ami qui est prof à la fac me demandait un cas concret d'utilisation légitime de trigger... Et j'ai été bien embêtée.
Voici la réponse que je lui ai faite :
D'après mon expérience, les triggers sont rarement une bonne idée. Il faut vraiment se poser la question de l'intérêt du trigger. Par exemple, s'il sert à mettre à jour des enregistrements (techniques ou pas), pourquoi le programme ne le ferait-il pas dans la même transaction ? S'il sert à implémenter une règle métier pourquoi les contraintes d'intégrité ne suffisent-elles pas ?
Les triggers sont souvent utilisés à tord et à travers par les éditeurs de progiciels (et plus à tord qu'à travers), ce qui pose des problèmes de réplication (avec golden gate par exemple), des problèmes de performance, des problèmes de maintenance et des vérolage (attention néologisme) de système quand les rollbacks n'ont pas été pensés. (Il faut savoir que les triggers sont rarement développés par des gens qui connaissent les bases de données.) Je ne parle même des casse-têtes que ça génère pour migrer sur un autre SGBD!

C'était mon idée, mais j'aurai aimé avoir une validation d'un expert reconnu dans le monde... et j'ai trouvé (très facilement) !
Voici pourquoi Tom Kyte n'aime pas les triggers : http://www.oracle.com/technetwork/issue-archive/2008/08-sep/o58asktom-101055.html et là pourquoi Vikas Munjal, invité de Paul Randal sur son blog SQL Authority n'aime pas non plus : http://blog.sqlauthority.com/2013/01/24/sql-server-how-to-use-instead-of-trigger-guest-post-by-vikas-munjal-koenig-solutions/

Bref, j'ai répondu à côté de la question...

Pour répondre à la question, à savoir trouver un cas d'utilisation réelle (à défaut de légitime) de triggers, voici ce que je propose. Il est de coutume de toujours ajouter 4 colonnes systématiquement dans les tables d'une modèle de données : creation_user, creation_date, updating_user, updating_date. Cela permet d'assurer la traçabilité des données de la table.
On pourrait envisager de créer un trigger remplissant automatiquement ces données. Dans la pratique, on ne le fera pas car, en plus des raisons indiquées plus haut, les logiciels utilisent un login non lié à l'utilisateur pour se connecter à la base et du coup "select user from dual;" renverrait toujours le user utilisé par le logiciel, et non le user de la personne ayant fait la modification...

Dans le même genre, il y a ici un exemple d'audit des mouvements de données sur une certaine table http://stackoverflow.com/questions/11261929/auditing-50-columns-using-oracle-trigger.

Enfin, les triggers peuvent être utilisés pour faire de la réplication, même si c'est extrêmement lourd à mettre en place, à maintenir et à utiliser, tout en étant extrêmement poussif en exécution.

Bref, je pense toujours que c'est une mauvaise idée et qu'il serait plus intéressant d'apprendre aux étudiants à écrire leurs requêtes avec la norme SQL d'après 1992 (c'est-à-dire avec le mot-clé JOIN) pour qu'on ne perde pas du temps à expliquer aux jeunes comment écrire une requête maintenable.

Voir les rôles/droits assignés à un user

C'est une demande récurrente. Pouvoir voir les rôles/droits accordés à un user. Si la base de données est bien gérée, tous les droits devraient être accordés via des rôles correctement nommés.

La vue dba_role_privs

Cette vue permet de données tous les rôles accordés et à qui ils ont été accordés. Les colonnes intéressantes sont celles-ci:

• grantee : Celui à qui on accorde le rôle
• granted_role : Le rôle autorisé

La vue dba_tab_privs

Mais comme dans la plupart des bases de données on trouve des droits accordés en dehors des rôles, il faut aller sur la vue dba_tab_privs. Cette vue permet de récupérer tous les droits accordés à un user sur un objet de la base. Les colonnes intéressantes sont celles-ci :

• grantee : Celui à qui on accorde des droits
• owner : Le propriétaire de l'objet sur lequel des droits ont été accordés
• table_name : Ce nom est ambigu, il s'agit bien de nom de l'objet (qui peut être une table ou pas)
• grantor : Celui qui a donné le droit
• privilege : Le droit accordé

Les vues role_*_privs

Si les rôles n'ont pas été correctement nommés et si la base n'est pas correctement documentée, il est possible d'oublier quels droits sont donnés par un rôle. C'est là que les vues role_role_privs, role_sys_privs et role_tab_privs peuvent vous aider. Malheureusement, le SQL ne permet pas de récursivité pour récupérer l'intégralité des rôles grantés à un autre rôle. Voir le script proposés après.

Tous les droits pour un user

La problématique est la même que pour les rôles. Il n'est pas possible de faire du récursif en SQL. Or, c'est nécessaire dans ce cas car on peut attribuer un rôle à un autre rôle un paquet de fois! Pour corriger ce problème, je vous conseille d'aller faire un tour sur le site de Pete Finnigan qui, même s'il a un sacré problème dans le choix des couleurs de son blog, a écrit une procédure stockée en PL/SQL qui peut déterminer l'ensemble des droits d'un rôle ou d'un user. http://www.petefinnigan.com/weblog/archives/00001243.htm

Pour en savoir plus

Vous pouvez regarder la définition des vues sur le livre Database Reference Oracle. En 11G : http://docs.oracle.com/cd/E11882_01/server.112/e40402/toc.htm