MS SQL Server - Triggers Instead Of dans les vues en mise à jour sur plusieurs tables

Introduction

Les vues modifiables (updatable views) peuvent modifier une ou plusieurs tables invoquées dans la vue. Les commandes INSERT, UPDATE et DELETE sur des vues peuvent être utilisées à condition que SQL Server soit capable de transcrire de manière non ambigüe les mises à jour sur les tables référencées dans la définition de la vue.

Des anomalies de comportement peuvent être observées lorsque l’on travaille sur des vues qui référencent plusieurs tables.

Cette documentation technique se propose de présenter une anomalie de comportement dans un contexte très particulier avec une solution de contournement mettant en œuvre les triggers Instead Of de SQL Server.

Contexte

Fonctionnalité des tables

Dans le contexte du problème qui se pose, deux tables :

• InterfaceDescription
• TextualDescriptions

Pour illustration, la table InterfaceDescription décrit des interfaces informatiques : chaque interface possède un identifiant, une brève description et 4 descriptions.

Les descriptions sont stockées dans la table TextualDescriptions qui ne contient que les 2 colonnes ci-dessous : l’identifiant de la description et la description stockée dans du type varchar(2000).

Cette séparation de tables a été implémentée pour éviter de mettre les 4 descriptions texte directement dans la table InterfaceDescription, ce qui impliquait une taille de ligne supérieure à 8K (taille maximale d’une ligne avec MS SQL Server 2000).

La vue v_descriptions

La vue v_descriptions effectue les jointures nécessaires pour l’affichage des 4 descriptions correspondant à chaque interface :

CREATE VIEW dbo.v_descriptions
AS
SELECT  InterfaceDescription.id,
        InterfaceDescription.InterfaceDescription as IntDesc ,
        TextualDescriptions.id AS id_text_1,
        TextualDescriptions.description_text AS desc_1,
        TextualDescriptions_1.id AS id_text_2,
        TextualDescriptions_1.description_text AS desc_2,
        TextualDescriptions_2.id AS id_text_3,
        TextualDescriptions_2.description_text AS desc_3,
        TextualDescriptions_3.id AS id_text_4,
        TextualDescriptions_3.description_text AS desc_4
        
FROM InterfaceDescription
   INNER JOIN TextualDescriptions 
      ON InterfaceDescription.desc_id_1=TextualDescriptions.id
   INNER JOIN TextualDescriptions TextualDescriptions_1 
      ON InterfaceDescription.desc_id_2 = TextualDescriptions_1.id
   INNER JOIN TextualDescriptions TextualDescriptions_2 
      ON InterfaceDescription.desc_id_3 = TextualDescriptions_2.id
   INNER JOIN TextualDescriptions TextualDescriptions_3 
      ON InterfaceDescription.desc_id_4 = TextualDescriptions_3.id

Les données de test sont les suivantes :

SELECT  * FROM v_descriptions

id   id_text_1   id_text_1   desc_1   id_text_2   desc_2   id_text_3   desc_3   id_text_4   desc_4
--   ----------- ---------   -------- ---------   -------- ---------   -------- ---------   ---------
 1   Interface 1         1   aa               2   aa               3   aa               4   aa
 2   Interface 2         5   aa               6   aa               7   aa               8   aa

Techniquement, des mises à jour sont envisagées à travers la vue v_descriptions pour mettre à jour la table TextualDescriptions pour une interface.

Erreurs dans la mise à jour de la vue v_descriptions

SQL Enterprise Manager

Avec SQL Enterprise Manager, la mise à jour d’une ou plusieurs colonnes de descriptions pour une interface à travers la vue v_descriptions engendre des erreurs.

Première erreur :

Data has changed since the Results pane was last updated. Do you want to save your changes now ?

Click Yes to save your changes and update the database.
Click No to discard your changes and refresh the Results pane.
Click Cancel to continue editing.

En cliquant sur Yes :

Seconde erreur :

Another user has modified the contents of this table or view; the database row you are modifying no longer exists
in the database.

Le profiler montre que la requête ci-dessous est exécutée :

exec sp_executesql N'

UPDATE "pubs"."dbo"."TextualDescriptions"
SET "description_text"=@P1
WHERE "id"=@P2
AND "description_text"=@P3
AND "id"=@P4
AND "description_text"=@P5
AND "id"=@P6
AND "description_text"=@P7
AND "id"=@P8
AND "description_text"=@P9',

N'@P1 varchar(255),@P2 int,@P3 varchar(255),@P4 int,@P5 varchar(255),@P6 int,@P7 varchar(255),@P8 int,@P9 varchar(255)',
'ab', 1, 'aa', 2, 'aa', 3, 'aa', 4, 'aa'

Ce qui peut se traduire avec la requête ci-dessous, requête qui ne met à jour aucune ligne quoiqu’il arrive compte tenu des clauses where :

UPDATE "pubs"."dbo"."TextualDescriptions"
SET "description_text"='ab'
WHERE "id"=1
AND "description_text"='aa'
AND "id"=2
AND "description_text"='aa'
AND "id"=3
AND "description_text"='aa'
AND "id"=4
AND "description_text"='aa'

Commandes Updates

Avec une commande update classique, une seule colonne à la fois peut être mise à jour. Lorsque la commande update concerne la mise à jour d’au moins deux colonnes de descriptions, une erreur est générée :

update v_descriptions
  set desc_1='aa',  desc_2='bb'
  where id=1
Server: Msg 4405, Level 16, State 2, Line 1
View or function 'v_descriptions' is not updatable because the modification affects multiple base tables.

Solution de contournement : les triggers Instead of

Le problème de la mise à jour des colonnes descriptions pour une interface à partir de la vue v_descriptions peut être contourné grâce aux triggers de type « Instead Of ».

Au lieu de demander au moteur SQL Server de lancer la commande update, seul le trigger sera déclenché, trigger dans lequel seront codées les mises à jour. Ceci est également vrai pour la commande insert.

Syntaxe :

CREATE TRIGGER trigger_name
ON { table | view }
[ WITH ENCRYPTION ]
{
{ { FOR | AFTER | INSTEAD OF} { [ INSERT ] [ , ] [ UPDATE] }
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
AS
[ { IF UPDATE ( column )
[ { AND | OR } UPDATE ( column ) ]
[ ...n ]
| IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask )
{ comparison_operator } column_bitmask [ ...n ]
} ]
sql_statement [ ...n ]
}
}

Classiquement dans le trigger déclenché avec une commande update, on retrouve les tables insertedet deleted contenant les anciennes et nouvelles valeurs.

Fonction columns_updated( )

La fonction columns_updated permet de savoir si une colonne est mise à jour ou pas. Si on considère les 8 premières colonnes d’une table ou d’une vue en mise à jour :

- Si la colonne 4 a été mise à jour : (ce qui correspond à 8 dans les puissances de 2)

columns_updated() & 8 = 8

- Si les colonnes 3 et 6 ont été mises à jour : (ce qui correspond à 4 + 32 dans la ligne power)

columns_updated() & 36 = 36

- Si la colonne 3 et/ou 6 a été mise à jour : (ce qui correspond à 4 + 32 dans la ligne power)

columns_updated() & 36 > 0

La règle ci-dessus s’applique parfaitement pour les 8 premières colonnes d’une table ou d’une vue mais qu’en est il des autres colonnes dont la position ordinale est supérieure à 8 ?

La règle est très simple : tout est relatif à la colonne ordinal_position de la vue INFORMATION_SCHEMA.COLUMNS pour une table donnée possédant plus de 8 colonnes.

select ordinal_position,
       round(ordinal_position/8 + 1,0) as "@bit",
       (ordinal_position-1)%8 +1 as "@field",
       power(2, (ordinal_position-1)%8) as "@power"
       
from INFORMATION_SCHEMA.COLUMNS
where table_name='<table_name>'

Ainsi, pour déterminer si la colonne 20 est mise à jour, colonne pour laquelle @bit vaut 3 et @field vaut 4, il faut à présent appliquer la fonction substring sur la valeur retournée par la fonction columns_updated avec la règle ci-dessous :

if (select substring(columns_updated(), @bit, 1) = power(2, @field -1))
begin
   …
end

Plus concrètement, pour déterminer si la colonne 20 est mise à jour, on doit avoir :

substring( columns_updated(), 3, 1 ) = 8.

Même principe pour déterminer si la colonne 14 est mise à jour, on doit avoir :

substring( columns_updated(), 2, 1 ) = 32.

Trigger "Instead Of Update" IOU_v_descriptions sur la vue v_descriptions

En T-SQL, la mise à jour de la table TextualDescriptions à travers le trigger 'Instead Of Update' sur la vue v_descriptions s’écrit alors ainsi (la gestion d’erreur et de rollback de la transaction en cas d’erreur est volontairement omise) :

CREATE TRIGGER IOU_v_descriptions ON [dbo].[v_descriptions]
instead of update
AS
/** Colonne 4 mise à jour ? */
if (substring(columns_updated(),1,1) & 8 = 8)
begin
       update TextualDescriptions
       set description_text = ins.desc_1
       from inserted ins
       where TextualDescriptions.id = ins.id_text_1
end
/** Colonne 6 mise à jour ? */
if (substring(columns_updated(),1,1) & 32 = 32)
begin
       update TextualDescriptions
       set description_text = ins.desc_2
       from inserted ins
       where TextualDescriptions.id = ins.id_text_2
end
/** Colonne 8 mise à jour ? */
if (substring(columns_updated(),1,1) & 128 = 128)
begin
       update TextualDescriptions
       set description_text = ins.desc_3
       from inserted ins
       where TextualDescriptions.id = ins.id_text_3
end
/** Colonne 10 mise à jour ? */
if (substring(columns_updated(),2,1) & 2 = 2)
begin
       update TextualDescriptions
       set description_text = ins.desc_4
       from inserted ins
       where TextualDescriptions.id = ins.id_text_4
end