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


1- 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 ambiguë 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.

2- Contexte

2-1- Fonctionnalité des tables

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

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

Colonne Type Clé ? Commentaire
Id integer (clé primaire) Identifiant de l'interface
InterfaceDescription varchar(255)
Brève description de l'interface
desc_id_1 integer
Identifiant de la description n°1
desc_id_2 integer
Identifiant de la description n°2
desc_id_3 integer
Identifiant de la description n°3
desc_id_4 integer
Identifiant de la description n°4

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).

Colonne Type Clé ? Commentaire
Id integer (clé primaire) Identifiant de l'interface
Description_text varchar(2000)
Description

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).

2-2- 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 Inthesc 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.

2-3- Erreurs dans la mise à jour de la vue v_descriptions

2-3-1- 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'

2-3-2- 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.

3- 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 inserted et deleted contenant les anciennes et nouvelles valeurs.

3-1- 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 :

  Col1 Col2 Col3 Col4 Col5 Col6 Col7 Col8
power 2^0=1 2^1=2 2^2=4 2^3=8 2^4=16 2^5=32 2^6=64 2^7=128

- 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>'

  Col1 Col2 Col3 Col4 Col5 Col6 Col7 Col8
ordinal_position 1 2 3 4 5 6 7 8
@bit round(ordinal_position/8 +1,0) 1 1 1 1 1 1 1 1
@field (ordinal_position-1)%8 + 1 1 2 3 4 5 6 7 8
@power 2^0=1 2^1=2 2^2=4 2^3=8 2^4=16 2^5=32 2^6=64 2^7=128
  Col9 Col10 Col11 Col12 Col13 Col14 Col15 Col16
ordinal_position 9 10 11 12 13 14 15 16
@bit round(ordinal_position/8 +1,0) 2 2 2 2 2 2 2 2
@field (ordinal_position-1)%8 + 1 1 2 3 4 5 6 7 8
@power 2^0=1 2^1=2 2^2=4 2^3=8 2^4=16 2^5=32 2^6=64 2^7=128
  Col17 Col18 Col19 Col20 Col21 Col22 Col23 Col24
ordinal_position 17 18 19 20 21 22 23 24
@bit round(ordinal_position/8 +1,0) 3 3 3 3 3 3 3 3
@field (ordinal_position-1)%8 + 1 1 2 3 4 5 6 7 8
@power 2^0=1 2^1=2 2^2=4 2^3=8 2^4=16 2^5=32 2^6=64 2^7=128

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.

3-2- 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  

A noter que l'implémentation de ce trigger n'évite absolument pas les erreurs lors d'une mise à jour avec SQL Enterprise manager. Le trigger en question corrige uniquement le problème dans le contexte de la commande update classique.


Annexe

Historique

Version Date Commentaires
1.0 06/2005 Version initiale

Liens

MSDN Books Online, SQL Server 2000, Periodicals, SQL Server 2000's INSTEAD OF Triggers