La gestion des erreurs se fait dans un bloc BEGIN TRY / END TRY. En cas d'erreurs, c’est le
bloc BEGIN CATCH / END CATCH qui gère l’erreur. Sur notre base de données « Papyrus »,
on crée une transaction qui permet d’insérer des lignes sur les tables ENTCOM et LIGCOM.
Si la transaction se passe bien, elle est validée, sinon elle est annulée.
-- On démarre une transaction et on lui donne un nom
BEGIN TRANSACTION transaction_CdeBande
-- Positionne le délai, en millisecondes
SET LOCK_TIMEOUT 2000
-- On déclare également la variable suivante
DECLARE @pcde int
BEGIN TRY
-- Opération d’insertion dans lignes de ventes
INSERT INTO [vente].[ENTCOM](OBSCOM,NUMFOU)
VALUES('Bande 09180',09180)
SELECT @pcde =@@IDENTITY
-- Commande de bande magnétique au fournisseur 09180
INSERT INTO [vente].[LIGCOM](NUMCOM,NUMLIG, CODART,QTECDE, PRIUNI, DERLIV)
VALUES(@pcde, 01, 'B001', 200, 140, Dateadd(mm,-1,getdate()) )
INSERT INTO [vente].[LIGCOM](NUMCOM,NUMLIG, CODART,QTECDE, PRIUNI, DERLIV)
VALUES(@pcde, 02, 'B002', 200, 140, Dateadd(mm,-2,getdate()) )
PRINT 'Transaction réussie'
COMMIT TRAN
END TRY
BEGIN CATCH
PRINT 'Transaction annulée'
ROLLBACK TRAN
END CATCH
Points d'enregistrements
184
Une autre chose qu'il est utile de connaître est le fait qu'on peut insérer des savepoint dans
une transaction, c'est-à-dire un point d'enregistrement, sur lequel on peut se baser pour faire
un ROLLBACK. Ainsi, on n'a pas besoin de faire une annulation sur l'intégralité de la
transaction, si on a deux parties bien distinctes dans notre script, on peut intercaler un point
d'enregistrement entre les 2. Si la première partie s'est déroulée sans problèmes, mais que la
deuxième partie s'est mal passée, on faire un ROLLBACK jusqu'à notre point
d'enregistrement puis un commit. Ainsi, tout ce qui s'est bien passé est validé et le reste est
annulé.
Pour sauvegarder un point d'enregistrement, il vous suffit de faire ceci :
-- On sauvegarde un point d'enregistrement pour la transaction
SAVE TRANSACTION nom_du_savepoint
Et si ensuite, vous voulez revenir à cet état, il vous suffit de faire :
-- On annule toutes les modifications de cette transaction jusqu'au point
d'enregistrement
ROLLBACK TRANSACTION nom_du_savepoint
Les déclencheurs
Les déclencheurs sont des objets anciens de SQL Server, qui permettent d’effectuer
automatiquement des tâches administratives de maintien de la base de données. En effet, les
déclencheurs, ou triggers, vont nous permettre, dans leur généralité, d’effectuer des
opérations automatiques, liées à une opération particulière sur un objet de la base, par
l’utilisateur. Plus précisément, lorsqu’un utilisateur va appliquer une opération du DML par
exemple, sur une table, si un déclencheur est défini sur cette table, une action automatique
en rapport avec l’action de l’utilisateur s’exécutera. On commence alors de parler de la notion
de programmation événementielle, en rapport au fait qu’un événement en déclenche un
autre.
Les déclencheurs du DML
Les déclencheurs sont donc des objets de la base qui nous permettent d’automatiser des
actions. Quelle est la particularité des déclencheurs du DML ? Ils vont nous permettre
d’effectuer une instruction, à chaque fois qu’une instruction du DML est effectuée pour une
table donnée. Prenons un exemple pour mieux comprendre : nous avons deux tables qui
fonctionnent ensemble, table1 et table2. Nous définissons un déclencheur du DML sur table1,
qui fait en sorte que, si la table table1 est mise à jour, les valeurs correspondantes dans
table2 sont mises à jour de la même manière. C’est de cette manière que nous allons
automatiser les actions sur les objets de la base. Pour une instruction donnée sur un objet,
une action liée s’exécutera à la suite.
Création d’un déclencheur du DML
La création de ce genre d’objet de la base ne peut se faire que par du code T-SQL, en
revanche, il est possible, comme pour tout autre objet de la base d’auto générer le modèle de
création d’un déclencheur. Pour ce faire, étendez les nœuds de l’explorateur d’objet jusqu'à la
table pour laquelle vous voulez créer le déclencheur, puis étendez la table choisie comme
suit :
185
Une fois cette opération effectuée, faites un clic droit sur le nœud « Déclencheurs » et
choisissez l’option « Nouveau déclencheur … ». Un code auto généré apparaît alors, dans
une nouvelle fenêtre de requêtes. Voici ce code :
CREATE TRIGGER <Schema_Name, sysname, Schema_Name>.<Trigger_Name, sysname,
Trigger_Name>
ON <Schema_Name, sysname, Schema_Name>.<Table_Name, sysname, Table_Name>
(AFTER | FOR | INSTEAD OF) <Data_Modification_Statements, ,
INSERT,DELETE,UPDATE>
[WITH APPEND][NOT FOR REPLICATION]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for trigger here
Expliquons ce code. Tout d’abord, l’instruction CREATE TRIGGER est l’instruction DDL qui va
nous permettre de créer un déclencheur. Il est nécessaire de préciser le nom du déclencheur
à la suite de cette instruction, puisque comme tout objet de la base, un déclencheur peut être
mentionné dans du code seulement grâce à son nom unique. La clause ON va nous permettre
de définir sur quelle table nous allons définir le déclencheur que nous créons et les clauses
AFTER, INSTEAD OF nous permettront par la suite, de données les conditions d’action du
déclencheur.
- AFTER : Le déclencheur s’exécutera après insertion, mise à jour ou suppression dans la
table qui contient le déclencheur.
- INSTEAD OF : Le déclencheur s’exécutera avant insertion, mise à jour ou suppression dans
la table qui contient le déclencheur.
Des options sont disponibles pour les triggers du DML, et elles sont les suivantes :
- WITH APPEND : cette option permet d’ajouter plusieurs déclencheurs sur un même objet
et un même ordre SQL. Ceci est dû à un comportement différent des déclencheurs au passage
de la version compatible 65 et 70.
- NOT FOR REPLICATION : Le déclencheur défini avec cette option ne sera pas pris en
compte dans un processus de modification des données par réplication.
Par exemple, après insertion dans la table précisée, faire ceci. Comme pour une fonction, les
délimiteurs des actions dans un déclencheur sont les clauses AS BEGIN et END. Tout le code
présent entre ces deux délimiteurs sera exécuté, à la seule condition que l’action déclenchant
le déclencheur soit faite auparavant. Prenons maintenant un exemple concret pour illustrer
nos propos. Dans le code du script proposé en annexe de ce cour, il y a un déclencheur du
DML nous allons l’utiliser ici comme exemple.
186
Remarque : Les instructions suivantes ne sont pas autorisées dans le corps du déclencheur,
à savoir CREATE, DROP, ALTER, TRUNCATE, GRANT, REVOKE, UPDATE STATISTICS,
RECONFIGURE et LOAD.
CREATE TRIGGER Soustraction_Stock
ON Commande
AFTER INSERT
AS
BEGIN
DECLARE @Id_Stock int, @Quantite int
SELECT @Id_Stock = Id_stock FROM INSERTED
SELECT @Quantite = Quantite FROM INSERTED
UPDATE [Stock]
SET [Quantite] = [Quantite]-@Quantite
WHERE Id_Stock = @Id_Stock
END
Au travers de ce code T-SQL, nous créons un déclencheur, dont le nom est
Soustraction_Stock, sur la table commande. Nous y associons l’événement AFTER INSERT,
qui précise qu’après insertions de données dans la table Commande, le code contenu entre les
délimiteurs AS BEGIN et END doit être exécuté. Le code contenu entre les délimiteurs, dans
cet exemple, récupère certaines valeurs insérées dans la table Commande, en l’occurrence
Id_Stock et Quantité, dans des variables déclarées en début de lot grâce aux mots clé FROM
INSERTED, et soustrait la quantité de la commande, à la quantité du stock, en fonction d’un
Id_Stock donné, grâce à un UPDATE de la table Stock. Les trois mots clé FROM UPDATED,
FROM INSERTED, FROM DELETED nous permettent de récupérer les valeurs insérées,
mises à jour ou supprimées, avant l’activation du déclencheur.
Les instructions de déclencheur DML utilisent deux tables spéciales : la table deleted et la
table inserted. SQL Server les crée et les gère automatiquement. Ces tables temporaires
résidant en mémoire servent à tester les effets de certaines modifications de données et à
définir des conditions pour les actions de déclenchement DML.
Exemple 1 : Création d’un déclencheur AFTER DELETE. À partir de notre base de données
« Papyrus », créer un déclencheur AFTER DELETE sur la table VENTE, pour empêcher
l’utilisateur de supprimer plusieurs lignes de ventes à la fois.
Nous allons étudier deux possibilités. La première est très simple. Nous utilisons la fonction
@@ROWCOUNT.
-- S'il existe déjà un trigger de ce nom
IF OBJECT_ID ('vente.Supp_Lignes_Vendre', 'TR') IS NOT NULL
-- Alors on le supprime
DROP TRIGGER vente.Supp_Lignes_Vendre;
GO
CREATE TRIGGER vente.Supp_Lignes_Vendre
-- Agit sur la table Vendre
ON vente.VENDRE
-- On précise qu'il s'agit d'un déclencheur pour DELETE
FOR DELETE
AS
BEGIN
IF @@ROWCOUNT > 1
BEGIN
-- On indique l'erreur
RAISERROR ('Trop de lignes à supprimer !', 10, 1)
-- Si erreur, on annule la transaction
ROLLBACK TRAN
187
-- On arrête le script
RETURN
END
END
@@ROWCOUNT Retourne le nombre de lignes affectées par la dernière instruction. Si le
nombre de lignes est supérieur à 2 milliards, utilisez @@ROWCOUNT_BIG.
Si nous testons ce trigger, on ne peut pas supprimer plusieurs lignes.
USE Papyrus
DELETE FROM vente.VENDRE
WHERE CODART like 'T%'
En revanche, on peut supprimer une ligne.
USE Papyrus
DELETE FROM vente.VENDRE
WHERE CODART = 'T001' AND NUMFOU = '9180'
La deuxième possibilité n’est pas à privilégier, mais nous permet d’introduire la notion de
curseur :
CREATE TRIGGER vente.Supp_Lignes_Vendre
-- Agit sur la table Vendre
ON vente.VENDRE
-- On précise qu'il s'agit d'un déclencheur pour DELETE
FOR DELETE
AS
-- On déclare nos variables
DECLARE @codart char(4)
DECLARE @nbrligne int
-- On désigne curseur_suppression le nom du curseur ouvert grâce auquel
s'effectue l'extraction
DECLARE curseur_suppression CURSOR FOR
-- Le cursor agit pour la sélection suivante
SELECT CODART, COUNT(*) FROM DELETED GROUP BY CODART
/* Ouvre un curseur de serveur Transact-SQL et remplit ce dernier en
exécutant
l'instruction Transact-SQL spécifiée dans l'instruction DECLARE CURSOR ou
SET
cursor_variable. */
OPEN curseur_suppression
-- Utilisation de FETCH : Récupère une ligne spécifique d'un curseur
FETCH curseur_suppression INTO @codart, @nbrligne
/* Into : Permet aux données issues des colonnes d'une extraction d'être
placées
dans des variables locales. Chaque variable de la liste (de gauche à
droite) est
associée à la colonne correspondante dans le jeu de résultats du curseur.
Le type
de données de chaque variable doit correspondre ou être une conversion
implicite
du type de données de la colonne du jeu de résultats correspondante. Le
nombre
de variables doit correspondre au nombre de colonnes dans la liste de
sélection
du curseur. */
-- Tant que @@FETCH_STATUS = 0
WHILE @@FETCH_STATUS = 0
188
/* @@FETCH_STATUS retourne l'état de la dernière instruction FETCH
effectuée sur un
curseur actuellement ouvert par la connexion. */
BEGIN
IF @nbrligne > 1
BEGIN
-- On indique l'erreur
RAISERROR ('Trop de lignes à supprimer pour article %s', 10, 1,@codart)
-- Si erreur, on annule la transaction
ROLLBACK TRAN
RETURN
END
FETCH curseur_suppression INTO @codart, @nbrligne
END
-- On ferme le curseur
CLOSE curseur_suppression
/* Supprime une référence de curseur. Une fois la dernière référence de
curseur désallouée, MicrosoftSQL Server libère les structures de données
contenant le curseur. */
DEALLOCATE curseur_suppression
Testons ce trigger :
USE Papyrus
DELETE FROM vente.VENDRE
WHERE CODART like 'I%'
Il est possible de voir les déclencheurs créés, en déployant les nœuds des tables prévus à cet
effet, comme montrés ci-dessous :
Ou encore en utilisant la procédure stockée sp_helptrigger. L'exemple suivant exécute
sp_helptrigger pour produire des informations sur le ou les déclencheurs de la table
vente.VRENDRE.
USE Papyrus;
GO
EXEC sp_helptrigger 'vente.VENDRE';
189
Exemple 2 : Création d’un déclencheur AFTER UPDATE. Pour le besoin de notre exemple,
nous créons une table ARTICLES_A_COMMANDER. Lorsque le stock physique augmenté de la
somme des quantités de l’article dans la table ARTICLES_A_COMMANDER et de la somme des
quantités de l’article dans la table LIGCDE devient inférieur au stock d’alertes, une nouvelle
ligne est insérée dans la table ARTICLES_A_COMMANDER.
Lorsque le stock physique est inférieur à 0, toute décrémentation est rejetée : la transaction
est annulée et un message est envoyé dans les journaux.
Nous créons la table ARTICLES_A_COMMANDER de structure : ARTICLES_A_COMMANDER
(CODART, QTE, DATECOM).
CREATE TABLE [vente].[ARTICLES_A_COMMANDER](
[CODART] [char](4)NOT NULL ,
[QTE] [smallint] NOT NULL,
[DATECOM] [date] NOT NULL)
Créer un déclencheur UPDATE sur la table PRODUIT de notre base de données « Papyrus » :
-- S'il existe déjà un trigger de ce nom
IF OBJECT_ID ('vente.ARTICLES_TRIG', 'TR') IS NOT NULL
-- Alors on le supprime
DROP TRIGGER vente.ARTICLES_TRIG;
GO
/* Lorsque le stock physique augmenté de la somme des quantités de
l’article dans la
table ARTICLES_A_COMMANDER et de la somme des quantités de l’article dans
la table
LIGCDE devient inférieur au stock d’alerte, une nouvelle ligne est insérée
dans la
table ARTICLES_A_COMMANDER ;
> CODART = CODART
> DATE = date du jour (par défaut)
> QTE = à définir */
CREATE TRIGGER vente.ARTICLES_TRIG
-- Agit sur la table PRODUIT
ON vente.PRODUIT
-- On précise qu'il s'agit d'un déclencheur pour UPDATE
FOR UPDATE
AS
-- On déclare nos variables
DECLARE @stockphysique int, @stockalerte int, @totalqtecommandee int,
@totalqte int
-- On incrémente notre variable @stockphysique et notre variable
@stockalerte
SELECT @stockphysique = p.STKPHY, @stockalerte = p.STKALE
FROM PRODUIT p
INNER JOIN inserted i
ON i.CODART = p.CODART
-- On ajoute une condition, si stocke est négatif
/* Lorsque le stock physique est inférieur à 0, toute décrémentation est
rejetée :
La transaction est annulée et un message est envoyé dans les journaux. */
190
IF (@stockphysique <= 0)
BEGIN
-- On indique l'erreur
RAISERROR ('Le stock est négatif !',16,1)
-- Si erreur, on annule la transaction
ROLLBACK TRAN
END
-- On incrémente notre variable @totalqtecommandee
SET @totalqtecommandee = isnull((select SUM(QTECDE)
FROM vente.LIGCOM l
INNER JOIN inserted i
ON l.CODART = i.CODART
GROUP BY l.CODART),0)
-- On incrémente notre variable @totalqte
SET @totalqte = isnull((select SUM(QTE)
FROM ARTICLES_A_COMMANDER a
INNER JOIN inserted i
ON a.CODART = i.CODART
GROUP BY a.CODART),0)
IF ((@stockphysique + @totalqtecommandee + @totalqte) < @stockalerte)
-- Alors on insère dans la table ARTICLES_A_COMMANDER
INSERT INTO ARTICLES_A_COMMANDER (CODART, QTE, DATECOM)
-- La sélection suivante
SELECT p.CODART, @stockalerte-@stockphysique + @totalqtecommandee +
@totalqte, getdate()
FROM PRODUIT p
INNER JOIN Inserted i
ON p.CODART = i.CODART
Pour comprendre le problème : Soit l’article « I120 », de stock d’alerte = 5, les tableaux ci-
dessous présentent les modifications de la table PRODUIT et les actions à effectuer sur la
table ARTICLES_A_COMMANDER, en fonction de la quantité en commande dans la table
LIGCOM.
Jeu d’essai n° 1 :
Jeu d’essai n° 2 :
Nous testerons les différents je d’essaie avec la requête suivante :
191
USE Papyrus
UPDATE vente.PRODUIT
SET STKPHY = STKPHY - 5
WHERE CODART = 'I120'
Exemple 3 : L'exemple suivant exécute sp_helptrigger pour produire des informations sur le
ou les déclencheurs de la table vente.VENDRE.
USE Papyrus;
GO
EXEC sp_helptrigger 'vente.VENDRE';
Modification d’un déclencheur du DML
Comme pour tout objet de la base, le déclencheur peut être modifié de deux manières, par
code T-SQL ou encore par l’interface graphique. Voyons les deux manières.
La structure générale de modification par code T-SQL est la même que pour n’importe quel
objet de la base puisque nous allons utiliser ALTER. Voyons un exemple :
USE [Papyrus]
GO
/****** Object: Trigger [vente].[Supp_Lignes_Vendre] Script Date:
01/27/2011 22:11:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [vente].[Supp_Lignes_Vendre]
ON [vente].[VENDRE]
FOR DELETE
AS
DECLARE @codart char(4)
DECLARE @nbrligne int
DECLARE curs_del CURSOR FOR
SELECT CODART, COUNT(*) FROM DELETED GROUP BY CODART
OPEN curs_del
FETCH curs_del INTO @codart, @nbrligne
WHILE @@FETCH_STATUS = 0
BEGIN
IF @nbrligne > 1
BEGIN
RAISERROR ('Trop de lignes à supprimer pour article %s', 10, 1,@codart)
ROLLBACK TRAN
RETURN
END
FETCH curs_del INTO @codart, @nbrligne
END
CLOSE curs_del
DEALLOCATE curs_del
