Petit guide de Transact SQL (v2000)

Date de publication : 16/04/2004

Par SQLPro (autres articles) (CV)
 

niveau : intermédiaire

Transact SQL, est une extension du dialecte SQL de SQL Server et constitue un langage procédural pour coder les procédures stockées et les triggers du SGBDR de Microsoft.
Le but de cet article est de présenter rapidement les concepts nécessaires à une bonne introduction à ce langage et ses possibilités.

1. Positionnement du langage

1.1. Historique

1.2. Utilisation

• dans les procédures stockées, pour les calculs portant sur des données internes à la base, sans considération de gestion d'IHM. Exemple pré calculs d'agrégats pour optimisation d'accès...
• dans les triggers, pour définir et étendre des règles portant sur le respect de l'intégrité des données. Exemple formatage de données, liens d'héritage exclusif entre tables filles...

1.3. Conclusion

2. Syntaxe

2.1. Identifiant

• ne peuvent dépasser 128 caractères.
• Ils doivent commencer par une lettre ou un "underscore".
• Les caractères spéciaux et le blanc ne sont pas admis.
• On se contentera d'utiliser les 37 caractères de base : ['A'..'Z', '0'..'9', '_']
• La casse n'a pas d'importance.

	SELECT CURRENT_TIMESTAMP as DateTime
	INTO #tempT
	
	SELECT * 
	FROM #tempT

	serveur.base_cible.utilisateur.objet

	SELECT * FROM MonServeur.MaBase.MonUser.MaTable

	base_cible..objet

	objet

2.2. Variables

bit, int, smallint, tinyint, decimal, numeric, money, smallmoney, float, real, datetime, smalldatetime, timestamp, 
uniqueidentifier, char, varchar, text, nchar, nvarchar, ntext, binary, varbinary, image.

cursor

DECLARE @maChaine char(32)

SET @maChaine = 'toto'

• il n'existe pas de type "tableau" dans le langage Transact SQL. Cependant, une table temporaire suffit à un tel usage.
• l'ordre SQL SELECT peut aussi servir à assigner une ou plusieurs variable, mais dans ce dernier cas il faut veiller à ce que la réponse à la requête ne produise qu'une seule ligne

SELECT @NomTable = 'TABLES', @NombreLigne = count(*)
FROM INFORMATION_SCHEMA.TABLES

DECLARE @Colonne varchar(8000)
SET @Colonne = ''
SELECT @Colonne = @Colonne +COALESCE(TABLE_NAME + ', ', '')
FROM INFORMATION_SCHEMA.TABLES

2.3. Structures basiques

BEGIN 
...
END

IF [NOT] condition instruction [ELSE instruction]
IF [NOT] EXISTS(requête select) instruction [ELSE instruction]
WHILE condition instruction
GOTO etiquette
WAITFOR [DELAY | TIME] temps

BREAK 
CONTINUE

• il n'y a pas de THEN dans le IF du Transact SQL, ni de DO dans le WHILE
• une seule instruction est permise pour les structure IF et WHILE, sauf à mettre en place un bloc d'instructions à l'aide d'une structure BEGIN / END
• un branchement sur étiquette se précise avec en suffixant l'identifiant de l'étiquette avec un caractère deux-points ':'

/* recherche de tous les nombres premiers de 1 à 5000 */
/* version procédurale (itérations)                   */
-- création d'une table provisoire pour stockage des données
create table #n (n int)

declare @n integer, @i integer, @premier bit
set @n = 1
set nocount on
-- un nombre premier n'est divisible que par 1 et lui même
while @n < 5000
BEGIN
-- on pré suppose qu'il est premier
   set @premier = 1
   set @i = 2
   while @i < @n
   BEGIN
-- autrement dit, tout diviseur situé entre 2 et lui même moins un
-- fait que ce nombre n'est pas premier
      if (@n / @i) * @i = @n
         SET @premier = 0
      SET @i = @i + 1
   END
   if @premier = 1
      insert into #n VALUES (@n)
   SET @n = @n + 1
END

SELECT * FROM #n

/* recherche de tous les nombres premiers de 1 à 5000 */
/* version ensembliste (requêtes)                     */
 DECLARE @max int
 SET @max = 5000
 -- cet exemple utilise la logique ensembliste pour calculer tous les nombres entiers
 SET NOCOUNT ON
 -- table temporaire de stockage des entiers de 1 à 5000
 CREATE TABLE #n (n int)
 -- boucle d'insertion de 0 à 5000
 DECLARE @i int
 SET @i = 0 
 WHILE @i < @max
 BEGIN
    INSERT INTO #n VALUES (@i)
    SET @i = @i + 1
 END
 -- on prend tous les entiers de la table n moins les entiers de la table n pour
 -- lesquels le reste de la division entière (modulo) par un entier moindre donne 0
 -- NOTA l'opération MODULO se note % dans Transact SQL
 SELECT distinct n
 FROM #n
 WHERE n not in (SELECT distinct n1.n
                 FROM   #n n1
                        CROSS JOIN #n n2
                 WHERE n1.n % n2.n = 0
                   AND n2.n BETWEEN 2 AND n1.n - 1)
 ORDER BY n

2.4. Variable de retour

RETURN 1445

2.5. Variables "système"

2.6. Flags

CREATE TABLE T_CLIENT
(CLI_ID INTEGER IDENTITY NOT NULL PRIMARY KEY,
 CLI_NOM VARCHAR(32))

SET IDENTITY_INSERT T_CLIENT ON

INSERT INTO T_CLIENT (CLI_ID, CLI_NOM) VALUES (325, 'DUPONT')
INSERT INTO T_CLIENT (CLI_ID, CLI_NOM) VALUES (987, 'MARTIN')

SET IDENTITY_INSERT T_CLIENT OFF

INSERT INTO T_CLIENT (CLI_ID, CLI_NOM) VALUES (512, 'LEVY')
=> Serveur: Msg 544, Niveau 16, État 1, Ligne 1
Impossible d'insérer une valeur explicite dans la colonne identité de la table 'T_CLIENT' quand IDENTITY_INSERT 
est défini à OFF.

2.7. Batch et GO

• Certains ordres ne peuvent être passés simultanément dans le même lot. Par exemple la suppression d'un objet (DROP) et sa re création immédiatement après, (CREATE) est source de conflits.
• Le mot clef GO n'est valable que pour une exécution par lot (batch). Il n'est donc pas reconnu au sein d'une procédure stockée ni dans le code d'un trigger

2.8. Quelques fonctions de base

CREATE DATABASE NEW_MABASE
ON 
( NAME = 'NEW_MABASE_DB',
  FILENAME = 'C:\MaBase.DATA',
  SIZE = 100,
  MAXSIZE = 500,
  FILEGROWTH = 50 )
LOG ON
( NAME = 'NEW_MABASE_LOG',
  FILENAME = 'C:\MaBase.LOG',
  SIZE = 5,
  MAXSIZE = 25,
  FILEGROWTH = 5 )
GO

USE NEW_MABASE
GO

CREATE TABLE T_CLIENT
(CLI_ID  INTEGER IDENTITY NOT NULL PRIMARY KEY,
 CLI_NOM VARCHAR(32))
GO

DECLARE
       @TableName Varchar(128),     -- nom de la table passé en argument
       @SearchWord Varchar(32)      -- mot recherché

Declare @ColumnList varchar(1000)   -- liste des noms de colonnes dans 
                                    -- lesquels la recherche va s'effectuer 
Declare @SQL varchar(1200)          -- requête de recherche

-- obtention de la liste des colonnes pour la requête de recherche
SELECT @ColumnList = COALESCE(@ColumnList + ' + COALESCE(', 'COALESCE(') + column_name +', '''')'
FROM   INFORMATION_SCHEMA.COLUMNS
WHERE  TABLE_NAME = @TableName
  AND  DATA_TYPE LIKE '%char%'

PRINT 'INFO - @ColumnList value is : ' + @ColumnList -- juste pour voir !

-- assemblage du texte de la requête de recherche 
Set @SQL =    'SELECT * FROM '+ @TableName 
           + ' WHERE '        + @ColumnList 
           + ' LIKE ''%'      + @SearchWord +'%'''

PRINT 'INFO - @SQL value is : ' + @SQL               -- juste pour voir !

-- exécution de la requête de recherche
Exec (@SQL)

2.9. Commentaires

3. UDF : fonction utilisateur

3.1. Fonction renvoyant une valeur

CREATE  FUNCTION [ utilisateur. ] nom_fonction
    ( [ { @parametre1[AS] type [ = valeur_défaut ] } [ , @parametre2 ... ] ] )

RETURNS type_résultant

[ AS ]

BEGIN

    code

    RETURN valeur_résultante

END

CREATE FUNCTION FN_PAQUE (@AN INT)
      
RETURNS DATETIME

AS

BEGIN

-- Algorithme conçu par Claus Tøndering .
-- Copyright (C) 1998 by Claus Tondering.
-- E-mail: claus@tondering.dk.

   IF @AN IS NULL
      RETURN NULL

   DECLARE @G INT
   DECLARE @I INT
   DECLARE @J INT
   DECLARE @C INT
   DECLARE @H INT
   DECLARE @L INT
   DECLARE @JourPaque INT
   DECLARE @MoisPaque INT
   DECLARE @DimPaque DATETIME

   SET @G = @AN % 19
   SET @C = @AN / 100
   SET @H = (@C - @C / 4 - (8 * @C + 13) / 25 + 19 * @G + 15) % 30
   SET @I = @H - (@H / 28) * (1 - (@H / 28) * (29 / (@H + 1)) * ((21 - @G) / 11))
   SET @J = (@AN + @AN / 4 + @I + 2 - @C + @C / 4) % 7

   SET @L = @I - @J
   SET @MoisPaque = 3 + (@L + 40) / 44
   SET @JourPaque = @L + 28 - 31 * (@MoisPaque / 4)

   SET @DimPaque = CAST(CAST(@AN AS VARCHAR(4)) +
                   CASE
                      WHEN @MoisPaque < 10 THEN '0' + CAST(@MoisPaque AS CHAR(1))
                                           ELSE       CAST(@MoisPaque AS CHAR(2))
                   END +
                   CASE
                      WHEN @JourPaque < 10 THEN '0' + CAST(@JourPaque AS CHAR(1))
                                           ELSE       CAST(@JourPaque AS CHAR(2))
                   END
                   AS DATETIME)

   RETURN @DimPaque

END

SELECT dbo.FN_PAQUE (2004) AS PAQUE_2004

PAQUE_2004                                            
-----------------------
2004-04-11 00:00:00.000

3.2. Fonction renvoyant une table

CREATE FUNCTION FN_JOUR_SEMAINE ()
RETURNS TABLE
AS
RETURN (SELECT 1 AS N, 'Lundi' AS JOUR
        UNION
        SELECT 2 AS N, 'Mardi' AS JOUR
        UNION
        SELECT 3 AS N, 'Mercredi' AS JOUR
        UNION
        SELECT 4 AS N, 'Jeudi' AS JOUR
        UNION
        SELECT 5 AS N, 'Vendredi' AS JOUR
        UNION
        SELECT 2 AS N, 'Samedi' AS JOUR
        UNION
        SELECT 2 AS N, 'Dimanche' AS JOUR)

SELECT *
FROM dbo.FN_JOUR_SEMAINE ()

N           JOUR    
----------- --------
1           Lundi
2           Dimanche
2           Mardi
2           Samedi
3           Mercredi
4           Jeudi
5           Vendredi

CREATE  FUNCTION [ utilisateur. ] nom_fonction
    ( [ { @parametre1[AS] type [ = valeur_défaut ] } [ , @parametre2 ... ] ] )

RETURNS type_résultant TABLE < definition_de_table >

[ AS ]

BEGIN
    code

    RETURN

END

CREATE FUNCTION FN_ENTIERS (@MAXINT integer, @NATUREL bit = 0)
       RETURNS @integers TABLE
               (N int PRIMARY KEY NOT NULL)
AS
BEGIN

   DECLARE @N INT
   DECLARE @T TABLE (N int)
   SET @N = 0

-- insertion des 10 premiers chiffres de 0 à 9
   WHILE @N < 10
   BEGIN
      INSERT INTO @T VALUES (@N)
      SET @N = @N + 1
   END
   SET @N = @N -1

-- si @N est supérieur à 9, alors supprimer les valeurs en trop
   IF @N > @MAXINT
      DELETE FROM @T WHERE N > @MAXINT
   ELSE
      INSERT INTO @T     
      SELECT DISTINCT 1 * T1.N + 10 * T2.N +  + 100 * T3.N + 1000  * T4.N
      FROM   @T AS T1
             CROSS JOIN @T AS T2
             CROSS JOIN @T AS T3
             CROSS JOIN @T AS T4
      WHERE  1 * T1.N + 10 * T2.N +  + 100 * T3.N + 1000  * T4.N BETWEEN 10 AND @MAXINT
     

-- s'il s'agit d'entiers naturels, supprimer le zéro  
   IF @NATUREL = 1
      DELETE FROM @T WHERE N = 0

-- insertion dans la variable de retour
   INSERT INTO @integers
   SELECT DISTINCT N FROM @T

   RETURN

END

SELECT *
FROM  dbo.FN_ENTIERS (13, 1)

N           
-----------
1
2
3
4
5
6
7
8
9
10
11
12
13

CREATE VIEW V_DATEHEURE_COURANTE
AS
SELECT CURRENT_TIMESTAMP AS DHC

CREATE FUNCTION FN_DELTA_MONTH (@MaDate DATETIME)
   RETURNS INT
AS
BEGIN
   DECLARE @N INT

   SELECT @N = DATEDIFF(MONTH, @MaDate, DHC)
   FROM   V_DATEHEURE_COURANTE

   RETURN @N
END

SELECT dbo.FN_DELTA_MONTH('20020101')

4. Procédures stockées

• étendre les possibilités des requêtes, par exemple lorsqu'un seul ordre INSERT ou SELECT ne suffit plus
• faciliter la gestion de transactions
• permettre une exécution plus rapide et plus optimisée de calculs complexes ne portant que sur des données de la base

• 128 Mo de code
• 1024 paramètres en argument (y compris curseurs)

4.1. Entête de procédure

CREATE PROCEDURE SP_SEARCH_STRING_ANYFIELD
       @TableName  Varchar(128),      -- nom de la table passé en argument
       @SearchWord Varchar(32)        -- mot recherché
AS
...

4.2. Paramètres, variables de retour et ensemble de données

CREATE PROCEDURE SP_SYS_DB_TRANSACTION 
                 @TRANS_NUM INTEGER,  -- numéro de la transaction concernée
                 @OK BIT = 0 OUTPUT   -- retour 0 OK, 1 problème
AS
...

DECLARE @RetourOK bit
EXEC(SP_SYS_DB_TRANSACTION 127, @RetourOK OUTPUT)
SELECT @RetourOK

CREATE PROCEDURE SEARCH_TEXT @resultTable CURSOR VARYING OUTPUT
AS

CREATE PROCEDURE SP_LISTE_CLIENT
                 @CLI_ID_DEBUT INTEGER, @CLI_ID_FIN INTEGER
AS

SELECT '-- début de liste client --' 
UNION  ALL
SELECT CLI_NOM 
FROM   T_CLIENT
WHERE  CLI_ID BETWEEN @CLI_ID_DEBUT AND @CLI_ID_FIN
UNION  ALL
SELECT '--  fin de liste client  --'

4.3. Gestion des erreurs

CREATE PROCEDURE SP_DELETE_CLIENT_RECURSIVE
                 @CLI_ID INTEGER

 /*---------------------------------------------------\
| recherche d'une occurrence de mot dans n'importe     |
| quelle colonne de type caractères d'une table donnée | 
|----------------------------------------------------- |
| Frédéric BROUARD - COMMUNICATIC SA - 2001-12-17      |
 \-------------------------------------------------- */

CREATE PROCEDURE SP_SEARCH_STRING_ANYFIELD
       @TableName Varchar(128),     -- nom de la table passé en argument
       @SearchWord Varchar(32)      -- mot recherché
AS 

IF @TableName IS NULL OR @SearchWord IS NULL
RAISERROR ('Paramètres NULL impossible à traiter', 16, 1)
IF @@ERROR <> 0 GOTO LBL_ERROR

-- test d'existence de la table
IF NOT EXISTS(SELECT *
              FROM   INFORMATION_SCHEMA.tables 
              WHERE  TABLE_NAME = @TableName)
RAISERROR ('Références de table inconnue %s', 16, 1, @TableName)
IF @@ERROR <> 0 GOTO LBL_ERROR

Declare @ColumnList varchar(1000)   -- liste des noms de colonnes dans 
                                    -- lesquels la recherche va s'effectuer 
Declare @SQL varchar(1200)          -- requête de recherche

-- obtention de la liste des colonnes pour la requête de recherche
SELECT @ColumnList = COALESCE(@ColumnList + ' + COALESCE(', 'COALESCE(') + column_name +', '''')'
FROM   INFORMATION_SCHEMA.COLUMNS
WHERE  TABLE_NAME = @TableName
  AND  DATA_TYPE LIKE '%char%'

IF @ColumnList IS NULL
RAISERROR ('Aucune colonne de recherche trouvé dans la table %s',
           16, 1, @TableName)
IF @@ERROR <> 0 GOTO LBL_ERROR

PRINT 'INFO - @ColumnList value is : ' + @ColumnList

-- assemblage du texte de la requête de recherche 
Set @SQL = 
   'SELECT * FROM '+ @TableName 
 + ' WHERE '       + @ColumnList 
 + ' LIKE ''%'     + @SearchWord +'%'''

PRINT 'INFO - @SQL value is : ' + @SQL

-- exécution de la requête de recherche
Exec (@SQL)

RETURN

LBL_ERROR:
PRINT 'ERREUR LORS DE L''EXÉCUTION DE LA PROCÉDURE STOCKÉE SP_SEARCH_STRING_ANYFIELD'

4.4. Procédures stockées prédéfinies

• de piloter le paramétrage du serveur et de la base de données (respect des normes SQL 2, maximum d'utilisateurs…) - exemple : sp_configure
• de définir les objets de la base (table, index, contraintes, vues…) - exemple : sp_addtype
• d'administrer le serveur, les bases, les accès (rôles, utilisateurs, login…) - exemple : sp_droplogin
• d'obtenir de l'aide et de la documentation (aide en ligne, explications) - exemple : sp_helpsort
• de gérer les agents (alertes et batch) - exemple : sp_add_job
• d'assurer les réplications (entre bases et serveurs) - exemple : sp_deleteMergeConflictRows
• de monitorer le fonctionnement de SQL Server (tracer l'exécution) - exemple : xp_sqlTrace
• ...

-- ajout du type TT_MONTH en entier obligatoire
SP_addType TT_MONTH, 'INTEGER', 'NOT NULL'

-- creation de la règle R_CHECK_MONTH vérifiant 
-- que la valeur soit comprise entre 1 et 12
CREATE RULE R_CHECK_MONTH AS @value BETWEEN 1 AND 12

-- liaison de la règle au type
SP_bindRule R_CHECK_MONTH, TT_MONTH

4.5. Verrouillage

• soit se débarrasser des contraintes de verrouillage, notamment pour faire du "dirty read" (lecture sale)
• soit poser des verrous plus contraignant pour s'assurer du traitement

SELECT *
FROM   T_CLIENT C WITH (NOLOCK)
       JOIN T_FACTURE F WITH (NOLOCK) 
            ON C.CLI_ID = F.CLI_ID

UPDATE T_CLIENT WITH (TABLOCK)
SET CLI_NOM = REPLACE(CLI_NOM, ' ', '-')

... WITH (PAGLOCK HOLDLOCK) ...

4.6. Gestion de transactions

BEGIN TRANSACTION

-- reste t-il de la place pour ce vol ?
IF NOT EXISTS(SELECT *
              FROM   T_VOL_AVION
              WHERE  VOL_REF = 'AF714'
                AND  VOL_PLACE_LIBRE > 3)
THEN
BEGIN
   ROLLBACK TRANSACTION
   RETURN
END

-- décompte des places vendues
UPDATE T_VOL_AVION
SET    VOL_PLACE_LIBRE  = VOL_PLACE_LIBRE - 3
WHERE  VOL_REF = 'AF714'
IF (@@ERROR <> 0) OR (@@ROWCOUNT = 0)
   GOTO ROLLBACK_ON_ERROR

-- génération d'une réservation
INSERT INTO T_RESERVATION (VOL_REF, CLI_ID, PLACES)
VALUES ('AF714', 123, 3)
IF (@@ERROR <> 0) OR (@@ROWCOUNT = 0)
   GOTO ROLLBACK_ON_ERROR

-- validation de l'ensemble
COMMIT TRANSACTION
RETURN

-- si branchement à l'étiquette d'erreur alors ROLLBACK
ROLLBACK_ON_ERROR:
   ROLLBACK TRANSACTION

READ UNCOMMITTED

READ COMMITTED

REPEATABLE READ

SERIALIZABLE

CREATE TABLE T_DEVELOPPEMENT_DEV 
(DEV_ID           INTEGER IDENTITY (1, 1) NOT NULL ,  -- identifiant 
 DEV_NIVEAU       SMALLINT                NOT NULL ,  -- niveau du noeud
 DEV_LIBELLE      CHAR (32)               NOT NULL ,  -- libellé
 DEV_BORNE_GAUCHE INTEGER                 NOT NULL ,  -- borne gauche de l'arbre
 DEV_BORNE_DROITE INTEGER                 NOT NULL)   -- borne droite de l'arbre

CREATE PROCEDURE SP_DEV_INSERTION_ARBRE_FILS_AINE
       @id_pere integer,
       @libelle varchar(32),
       @id_ins  integer OUTPUT AS

-- Frédéric BROUARD 17/11/2001
-- insertion sous père dans l'arbre du développement
-- paramètres : id_pere, libelle

DECLARE @bg_pere integer 
DECLARE @niveau integer
DECLARE @OK integer

SET NOCOUNT ON

-- démarrage transaction 
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION INSERT_TREE_FILS_AINE

-- vérification de l'existence du père
SELECT @OK = count(*)
FROM T_DEVELOPPEMENT_DEV
WHERE DEV_ID = @id_pere

-- si élément supprimé, alors retour sans insertion avec valeur -1
IF @OK = 0
BEGIN
   SELECT -1
   ROLLBACK TRANSACTION INSERT_TREE_FILS_AINE
   RETURN
END

-- recherche du bg_pere et calcul du niveau inférieur
SELECT @bg_pere = DEV_BORNE_GAUCHE, @niveau = DEV_NIVEAU + 1
FROM T_DEVELOPPEMENT_DEV
WHERE DEV_ID = @id_pere

-- décalage pour insertion borne droite
UPDATE T_DEVELOPPEMENT_DEV 
SET DEV_BORNE_DROITE = DEV_BORNE_DROITE + 2 
WHERE DEV_BORNE_DROITE > @bg_pere

IF @@ERROR <>0
BEGIN
   ROLLBACK TRANSACTION INSERT_TREE_FILS_AINE
   SELECT -1
   RETURN
END

-- décalage pour insertion borne gauche
UPDATE T_DEVELOPPEMENT_DEV 
SET DEV_BORNE_GAUCHE = DEV_BORNE_GAUCHE + 2 
WHERE DEV_BORNE_GAUCHE > @bg_pere

IF @@ERROR <>0
BEGIN
   ROLLBACK TRANSACTION INSERT_TREE_FILS_AINE
   SELECT -1
   RETURN
END

-- insertion
INSERT INTO T_DEVELOPPEMENT_DEV (DEV_NIVEAU, DEV_LIBELLE, DEV_BORNE_GAUCHE, DEV_BORNE_DROITE) 
       VALUES (@niveau, @libelle, @bg_pere + 1, @bg_pere + 2) 

SELECT @id_ins = @@identity

IF @@ERROR <>0
BEGIN
   ROLLBACK TRANSACTION INSERT_TREE_FILS_AINE
   SELECT -1
   RETURN
END

COMMIT TRANSACTION INSERT_TREE_FILS_IANE

SELECT @id_ins  -- renvoie de l'identifiant de l'élément inséré.

CREATE PROCEDURE SP_DEV_INSERTION_ARBRE_FILS_CADET
       @id_pere integer,
       @libelle varchar(32),
       @id_ins  integer      OUTPUT AS

-- Frédéric BROUARD 17/11/2001
-- insertion sous père dans l'arbre du développement
-- paramètres : id_pere, libelle

DECLARE @bd_pere integer
DECLARE @niveau integer
DECLARE @OK integer

SET NOCOUNT ON

-- on gère une transaction qui remanie les bornes de l'arbre.
-- il ne faut pas être 'dérangé' par d'autres utilisateurs concurrents pendant cette manoeuvre
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION INSERT_TREE_FILS_CADET

-- vérification de l'existence du père
SELECT @OK = count(*)
FROM T_DEVELOPPEMENT_DEV
WHERE DEV_ID = @id_pere

-- si élément supprimé, alors retour sans insertion avec valeur -1
IF @OK = 0
BEGIN
   SELECT -1
   ROLLBACK TRANSACTION INSERT_TREE_FILS_CADET
   RETURN
END

-- recherche du bg_pere et calcul du niveau inférieur
SELECT @bd_pere = DEV_BORNE_DROITE, @niveau = DEV_NIVEAU + 1
FROM T_DEVELOPPEMENT_DEV
WHERE DEV_ID = @id_pere

-- décalage pour insertion borne droite
UPDATE T_DEVELOPPEMENT_DEV 
SET DEV_BORNE_DROITE = DEV_BORNE_DROITE + 2 
WHERE DEV_BORNE_DROITE >= @bd_pere

IF @@ERROR <>0
BEGIN
   ROLLBACK TRANSACTION INSERT_TREE_FILS_CADET
   SELECT -1
   RETURN
END

-- décalage pour insertion borne gauche
UPDATE T_DEVELOPPEMENT_DEV 
SET DEV_BORNE_GAUCHE = DEV_BORNE_GAUCHE + 2 
WHERE DEV_BORNE_GAUCHE > @bd_pere

IF @@ERROR <>0
BEGIN
   ROLLBACK TRANSACTION INSERT_TREE_FILS_CADET
   SELECT -1
   RETURN
END

-- insertion
INSERT INTO T_DEVELOPPEMENT_DEV (DEV_NIVEAU, DEV_LIBELLE, DEV_BORNE_GAUCHE, DEV_BORNE_DROITE) 
       VALUES (@niveau, @libelle, @bd_pere , @bd_pere + 1)

SELECT @id_ins = @@identity

IF @@ERROR <>0
BEGIN
   ROLLBACK TRANSACTION INSERT_TREE_FILS_CADET
   SELECT -1 as ID
   RETURN
END

COMMIT TRANSACTION INSERT_TREE_SON

SELECT @id_ins

CREATE PROCEDURE SP_DEV_INSERTION_ARBRE_FRERE_DROIT
       @id_frere integer,
       @libelle  varchar(32),
       @id_ins   integer       OUTPUT AS

-- Frédéric BROUARD 17/11/2001
-- insertion latérale à droite du frère
-- paramètres : id_frere, libelle

DECLARE @bd_frere integer
DECLARE @niveau integer
DECLARE @OK integer

SET NOCOUNT ON

-- démarrage transaction
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION INSERT_TREE_RIGHT

-- vérifie de l'existence du frère
SELECT @OK = count(*)
FROM T_DEVELOPPEMENT_DEV
WHERE DEV_ID = @id_frere

-- si élément supprimé, alors retour sans insertion avec valeur -1
IF @OK = 0
BEGIN
   SELECT -1
   ROLLBACK TRANSACTION INSERT_TREE_RIGHT
   RETURN
END

-- recherche du bd_frere et du niveau
SELECT @bd_frere = DEV_BORNE_DROITE, @niveau = DEV_NIVEAU 
FROM T_DEVELOPPEMENT_DEV
WHERE DEV_ID = @id_frere

-- décalage borne gauche pour insertion
UPDATE T_DEVELOPPEMENT_DEV 
SET    DEV_BORNE_GAUCHE = DEV_BORNE_GAUCHE + 2 
WHERE  DEV_BORNE_GAUCHE > @bd_frere

IF @@ERROR <>0
BEGIN
   ROLLBACK TRANSACTION  INSERT_TREE_RIGHT
   SELECT -1
   RETURN
END

-- décalage borne droite pour insertion
UPDATE T_DEVELOPPEMENT_DEV 
SET DEV_BORNE_DROITE = DEV_BORNE_DROITE + 2
WHERE DEV_BORNE_DROITE > @bd_frere

IF @@ERROR <>0
BEGIN
   ROLLBACK TRANSACTION  INSERT_TREE_RIGHT
   SELECT -1
   RETURN
END

-- insertion
INSERT INTO T_DEVELOPPEMENT_DEV (DEV_NIVEAU, DEV_LIBELLE, DEV_BORNE_GAUCHE, DEV_BORNE_DROITE) 
       VALUES (@niveau, @libelle, @bd_frere + 1, @bd_frere+2) 

SELECT @id_ins = @@identity

IF @@ERROR <>0
BEGIN
   ROLLBACK TRANSACTION  INSERT_TREE_RIGHT
   SELECT -1
   RETURN
END

COMMIT TRANSACTION INSERT_TREE_RIGHT

SELECT @id_ins

CREATE PROCEDURE SP_DEV_INSERTION_ARBRE_FRERE_GAUCHE
       @id_frere integer,
       @libelle  varchar(32),
       @id_ins   integer OUTPUT AS

-- Frédéric BROUARD 17/11/2001
-- insertion latérale à gauche du frère
-- paramètres : id_frere, libelle

DECLARE @bg_frere integer
DECLARE @niveau integer
DECLARE @OK integer

SET NOCOUNT ON

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION INSERT_TREE_LEFT

-- vérification de l'existence du frère
SELECT @OK = count(*)
FROM T_DEVELOPPEMENT_DEV
WHERE DEV_ID = @id_frere

-- si élément supprimé, alors retour sans insertion avec valeur -1
IF @OK = 0
BEGIN
   SELECT -1
   ROLLBACK TRANSACTION INSERT_TREE_LEFT
   RETURN
END

-- recherche du bg_frere
SELECT @bg_frere = DEV_BORNE_GAUCHE, @niveau = DEV_NIVEAU 
FROM T_DEVELOPPEMENT_DEV
WHERE DEV_ID = @id_frere

-- décalage borne gauche pour insertion
UPDATE T_DEVELOPPEMENT_DEV 
SET   DEV_BORNE_GAUCHE = DEV_BORNE_GAUCHE + 2 
WHERE DEV_BORNE_GAUCHE >= @bg_frere

IF @@ERROR <>0
BEGIN
   ROLLBACK TRANSACTION INSERT_TREE_LEFT
   SELECT -1
   RETURN
END

-- décalage borne droite pour insertion
UPDATE T_DEVELOPPEMENT_DEV 
SET DEV_BORNE_DROITE = DEV_BORNE_DROITE + 2
WHERE DEV_BORNE_DROITE > @bg_frere

IF @@ERROR <>0
BEGIN
   ROLLBACK TRANSACTION INSERT_TREE_LEFT
   SELECT -1
   RETURN
END

-- insertion
INSERT INTO T_DEVELOPPEMENT_DEV (DEV_NIVEAU, DEV_LIBELLE, DEV_BORNE_GAUCHE, DEV_BORNE_DROITE) 
       VALUES (@niveau, @libelle, @bg_frere, @bg_frere+1) 

SELECT @id_ins = @@identity

IF @@ERROR <>0
BEGIN
   ROLLBACK TRANSACTION
   SELECT -1
   RETURN
END

COMMIT TRANSACTION INSERT_TREE_LEFT

SELECT @id_ins

CREATE PROCEDURE SP_DEV_SUPPRESSION
       @id_element integer,
       @recursif   bit
AS

-- Frédéric BROUARD 20/11/2001
-- suppression d'un élément (@recursif = 0) ou d'un sous arbre (@recursif = 1)
-- paramètres : id_element, @recursif

DECLARE @OK integer
DECLARE @bg_element integer
DECLARE @bd_element integer
DECLARE @intervalle integer
 

SET NOCOUNT ON

-- démarrage transaction
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION DELETE_TREE

-- vérifie de l'existence de l'élément
SELECT @OK = count(*)
FROM T_DEVELOPPEMENT_DEV
WHERE DEV_ID = @id_element

-- si élément supprimé, alors retour sans insertion avec valeur -1
IF @OK = 0
BEGIN
   SELECT -1
   ROLLBACK TRANSACTION DELETE_TREE
   RETURN
END
 

-- recherche des bd_element et bd_element
SELECT @bd_element = DEV_BORNE_DROITE, @bg_element = DEV_BORNE_GAUCHE
FROM T_DEVELOPPEMENT_DEV
WHERE DEV_ID = @id_element

IF @recursif = 0
BEGIN
-- suppression de l'élément 
   DELETE FROM T_DEVELOPPEMENT_DEV
   WHERE  DEV_ID = @id_element

   IF @@ERROR <>0
   BEGIN
      ROLLBACK TRANSACTION  DELETE_TREE
      SELECT -1
      RETURN
   END

-- décalage des bords droits et gauches des éléments du sous arbre
   UPDATE T_DEVELOPPEMENT_DEV
   SET DEV_BORNE_DROITE = DEV_BORNE_DROITE - 1,  DEV_BORNE_GAUCHE = DEV_BORNE_GAUCHE - 1
   WHERE DEV_BORNE_GAUCHE > @bg_element AND DEV_BORNE_DROITE < @bd_element

   IF @@ERROR <>0
   BEGIN
      ROLLBACK TRANSACTION  DELETE_TREE
      SELECT -1
      RETURN
   END

-- décalage des bords gauches des éléments externes au sous arbre
   UPDATE T_DEVELOPPEMENT_DEV
   SET    DEV_BORNE_GAUCHE = DEV_BORNE_GAUCHE - 2
   WHERE  DEV_BORNE_GAUCHE > @bg_element 

   IF @@ERROR <>0
   BEGIN
      ROLLBACK TRANSACTION  DELETE_TREE
      SELECT -1
      RETURN
   END

-- décalage des bords droits des éléments externes au sous arbre
   UPDATE T_DEVELOPPEMENT_DEV
   SET    DEV_BORNE_DROITE = DEV_BORNE_DROITE - 2
   WHERE  DEV_BORNE_DROITE < @bg_element

   IF @@ERROR <>0
   BEGIN
      ROLLBACK TRANSACTION  DELETE_TREE
      SELECT -1
      RETURN
   END
END

IF @recursif = 1
BEGIN
-- suppression des éléments du sous arbre
   DELETE FROM T_DEVELOPPEMENT_DEV
   WHERE  DEV_BORNE_GAUCHE >= @bg_element AND DEV_BORNE_DROITE <= @bd_element

   IF @@ERROR <>0
   BEGIN
      ROLLBACK TRANSACTION  DELETE_TREE
      SELECT -1
      RETURN
   END

-- calcul de l'intervalle de décallage
   SET @intervalle = @bd_element - @bg_element + 1

-- décalage des bords gauches des éléments externes au sous arbre
   UPDATE T_DEVELOPPEMENT_DEV
   SET    DEV_BORNE_GAUCHE = DEV_BORNE_GAUCHE - @intervalle
   WHERE  DEV_BORNE_GAUCHE > @bg_element 

   IF @@ERROR <>0
   BEGIN
      ROLLBACK TRANSACTION  DELETE_TREE
      SELECT -1
      RETURN
   END

-- décalage des bords droits des éléments externes au sous arbre
   UPDATE T_DEVELOPPEMENT_DEV
   SET    DEV_BORNE_DROITE = DEV_BORNE_DROITE - @intervalle
   WHERE  DEV_BORNE_DROITE < @bg_element

   IF @@ERROR <>0
   BEGIN
      ROLLBACK TRANSACTION  DELETE_TREE
      SELECT -1
      RETURN
   END

END

COMMIT TRANSACTION  DELETE_TREE

4.7. Les curseurs

-- déclaration des variables de colonnes pour le curseur
DECLARE @Col1 Type1, @Col2 Type2, @Col3, Type3...

-- declaration du curseur
DECLARE MyCursor CURSOR 
FOR 
   SELECT COL1, COL2, COL3 …
   FROM MyTable

-- ouverture du curseur
OPEN MyCursor

-- lecture du premier enregistrement
FETCH MyCursor INTO @Col1, @Col2, @Col3...

-- boucle de traitement
WHILE @@fetch_Status = 0
BEGIN
   traitement
-- lecture de l'enregistrement suivant
   FETCH MyCursor INTO @Col1, @Col2, @Col3...
END

-- fermeture du curseur
CLOSE myCursor

-- libération de la mémoire
DEALLOCATE myCursor

DECLARE nom_curseur [INSENSITIVE] [SCROLL] CURSOR 
FOR requête_select
FOR {READ ONLY | UPDATE [OF colonne1 [, colonne2...]]}]

DECLARE nom_curseur CURSOR [LOCAL | GLOBAL] FORWARD_ONLY | SCROLL] [STATIC | KEYSET | DYNAMIC | FAST_FORWARD] READ_ONLY | 
					SCROLL_LOCKS | OPTIMISTIC] [TYPE_WARNING]
FOR requête_select
FOR UPDATE [OF colonne1 [, colonne2...]]]

/***************************************************/
-- Frédéric BROUARD - Communicatic SA - 2001-12-24 --
--=================================================--
-- Informe du nombre de ligne de chaque table de   --
-- la base de données                              --
/***************************************************/

CREATE PROCEDURE SP_SYS_DB_DATA_ROWS
AS

-- variables locales de la procédure
DECLARE @NomTable VARCHAR(128),     -- nom de la table
        @SQL      VARCHAR(1000)     -- texte de la requête dynamique

-- pas de messages intempestifs
SET NOCOUNT ON

-- déclaration du curseur pour analyse des tables de la base
DECLARE CursBase CURSOR 
FOR
   SELECT TABLE_NAME
   FROM   INFORMATION_SCHEMA.tables
   WHERE  TABLE_TYPE = 'BASE TABLE'

-- gestion de la table des résultats : nom de la table temporaire
SET @NomTable = '#DATA_VOLUME'

-- vidage si existence de cette table, sinon création
IF EXISTS(SELECT * FROM   INFORMATION_SCHEMA.tables WHERE  TABLE_NAME = @NomTable)
   DELETE FROM #DATA_VOLUME
ELSE
   CREATE TABLE #DATA_VOLUME
   (TABLE_NAME  VARCHAR(128),
    DATA_ROWS   INTEGER)

-- ouverture du curseur
OPEN CursBase

-- lecture de la première ligne
FETCH CursBase INTO @NomTable

-- boucle ligne à ligne
WHILE @@FETCH_STATUS = 0
BEGIN
-- requête d'insertion avec recherche du nombre de ligne de la table analysée
   SET @SQL = 'INSERT INTO #DATA_VOLUME SELECT '''
              + @NomTable + ''', COUNT(*) FROM ' + @NomTable
   EXEC(@SQL)
   FETCH CursBase INTO @NomTable
END

-- fermeture et désallocation d'espace mémoire du curseur
CLOSE CursBase
DEALLOCATE CursBase

-- envoi des données
SELECT * FROM #DATA_VOLUME

-- pas de messages intempestifs
SET NOCOUNT OFF

 /*---------------------------------------------------\
| recherche d'une occurrence de mot dans n'importe     |
| quelle colonne de type caractères de n'importe       |
| quelle table de la base de données                   | 
|----------------------------------------------------- |
| Frédéric BROUARD - COMMUNICATIC SA - 2001-12-18      |
 \-------------------------------------------------- */

CREATE PROCEDURE SP_SEARCH_STRING_ANYFIELD_ANYTABLE
                 @SearchWord Varchar(32)      -- mot recherché
AS 

DECLARE @ErrMsg VARCHAR(128)

-- effet de bord 1 : pas de mot passé
IF @SearchWord IS NULL
BEGIN
   SET @ErrMsg = 'Impossible de traiter cette recherche avec un argument NULL'
   GOTO LBL_ERROR 
END

-- effet de bord 2 : mot vide passé
IF @SearchWord = ''
BEGIN
   SET @ErrMsg = 'Impossible de traiter cette recherche avec un argument vide'
   GOTO LBL_ERROR 
END

-- effet de bord 3 : mot contenant un caractère joker % du LIKE
IF CHARINDEX('%', @SearchWord) > 0
BEGIN
   SET @ErrMsg = 'Impossible de traiter cette recherche avec un argument contenant un caractère %'
   GOTO LBL_ERROR 
END

-- effet de bord 4 : mot contenant un caractère joker _ du LIKE
IF CHARINDEX('_', @SearchWord) > 0
BEGIN
   SET @ErrMsg = 'Impossible de traiter cette recherche avec un argument contenant un caractère _'
   GOTO LBL_ERROR 
END

-- variables de travail
DECLARE @TableName   VARCHAR(128), -- nom de la table passé en argument
        @ColumnList1 VARCHAR(2000),-- liste des colonnes pour clause SELECT
        @ColumnList2 VARCHAR(2000),-- liste des colonnes pour clause WHERE
        @SQL         VARCHAR(5000) -- requête dynamique 

-- curseur parcourant toutes les tables
DECLARE CurTables CURSOR
FOR 
   SELECT DISTINCT TABLE_NAME
   FROM   INFORMATION_SCHEMA.tables
   WHERE  TABLE_TYPE = 'BASE TABLE'
     AND  TABLE_NAME IS NOT NULL
-- en cas d'erreur
IF @@Error <> 0
BEGIN
   SET  @ErrMsg = 'Erreur dans la recherche de la lsite des tables concernées'
   GOTO LBL_ERROR 
END

-- ouverture du cuseur
OPEN CurTables

-- lecture de la première ligne de l'ensemble de résultat
FETCH CurTables INTO @TableName

-- la lecture est-elle correcte ? Oui, on boucle !
WHILE @@Fetch_Status = 0
BEGIN

-- les variables contenant les listes des colonnes sont initialisée à vide
   SET @ColumnList1 = ''
   SET @ColumnList2 = ''

-- construction des listes
   SELECT @ColumnList1 = @ColumnList1 + COLUMN_NAME+', ',
          @ColumnList2 = @ColumnList2 + 'COALESCE('+COLUMN_NAME+', '''') + '
   FROM   INFORMATION_SCHEMA.COLUMNS
   WHERE  TABLE_NAME = @tableName
     AND  DATA_TYPE LIKE '%char%'

-- pas de colonnes cible pour la recherche, on lit l'enregistrement suivant et on boucle
   IF @ColumnList1 = ''
   BEGIN
      FETCH CurTables INTO @TableName
      CONTINUE
   END

-- suppression du dernier caractère parasite des listes de colonne 
   SET @ColumnList1 = SUBSTRING(@ColumnList1, 1, LEN(@ColumnList1) - 1)
   SET @ColumnList2 = SUBSTRING(@ColumnList2, 1, LEN(@ColumnList2) - 1)

-- création de la requête de recherche de l'ensemble des occurences
   SET @SQL = 'SELECT ' +@ColumnList1
            +' FROM '   +@TableName
            +' WHERE '  +@ColumnList2
            +' LIKE ''%'+@SearchWord+'%'''

-- exécution de la requête de recherche des occurences
   EXEC(@SQL)

-- lecture de la ligne suivante
   FETCH CurTables INTO @TableName
END

-- fermeture du curseur
CLOSE CurTables

-- libération de l'espace mémoire
DEALLOCATE CurTables

PRINT '*** RECHERCHE de l''occurence '+@SearchWord+ ' dans toute la base terminée ***'

RETURN

-- gestion des erreurs
LBL_ERROR:
RAISERROR (@ErrMsg, 16, 1)

-- déclaration d'un curseur pour mise à jour
DECLARE NomCurseur CURSOR
FOR 
   SELECT ...
   FROM LaTable
   ...
FOR UPDATE
...
-- exécution de la mise à jour sous curseur
UPDATE LaTable
SET Colonne1 = ..., Colonne2 = ...
WHERE CURRENT OF NomCurseur

5. Les triggers

• l'insertion de données (INSERT)
• la suppression de données (DELETE)
• la mise à jour (UPDATE)

5.1. Mise en place d'un trigger

5.2. Syntaxe d'un trigger MS SQL Server 2000

CREATE TRIGGER <nom_trigger>
ON <table_ou_vue>
   FOR | AFTER | INSTEAD
   OF  [ INSERT ] [ , ] [ UPDATE ] [ , ] [DELETE]
AS
<code>

• gestion d'héritage avec lien d'exclusion
• suppression, insertion et mise à jour en cascade
• contrôle de validité
• respect d'intégrité complexes
• formatage de données
• archivage automatique
• ...

5.3. Eléments du langage spécifique aux triggers

5.3.1. Pseudo tables INSERTED et DELETED

5.3.2. Fonctions UPDATE et COLUMNS_UPDATED

IF [NOT] UPDATE(<colonne>)
BEGIN
   <traitement>
END

IF [NOT] (COLUMNS_UPDATED() & <masque ordinal>) <comparateur> <valeur masque attendue>
BEGIN
   <traitement>
END

CREATE TABLE T_PROSPECT
(PSP_ID          INTEGER,
 PSP_NOM         CHAR(32),
 PSP_PRENOM      VARCHAR(25),
 PSP_SAISIE      DATETIME,
 PSP_TEL         VARCHAR(20))

PSP_ID      PSP_NOM         PSP_PRENOM         PSP_SAISIE       PSP_TEL 
----------- --------------- ------------------ ---------------- -------------------- 
1           2               3                  4                5

SELECT COLUMN_NAME, ORDINAL_POSITION
FROM   INFORMATION_SCHEMA.COLUMNS
WHERE  TABLE_NAME = 'T_PROSPECT'

COLUMN_NAME        ORDINAL_POSITION 
----------------------------------- 
PSP_ID             1
PSP_NOM            2
PSP_PRENOM         3
PSP_SAISIE         4
PSP_TEL            5

IF (COLUMNS_UPDATED() & 22) > 0
BEGIN
   <traitement>
END

IF (COLUMNS_UPDATED() & 22) = 22
BEGIN
   <traitement>
END

colonne     : PSP_ID     PSP_NOM    PSP_PRENOM PSP_SAISIE PSP_TEL 
              ---------- ---------- ---------- ---------- ----------
ordinal     : 1          2          3          4          5
puissance 2 : 2^0 = 1    2^1 = 2    2^2 = 4    2^3 = 8 2^4 = 16
retenu      : non        oui        oui        non        oui
valeur      : 0     2         4          0          16          = 22 (SOMME de 16 + 4 + 2)

5.3.3. Annulation des effets d'un trigger

5.4. Exemples de triggers

CREATE TRIGGER E_CLI_INS
       ON  T_CLIENT
       FOR INSERT, UPDATE
AS
-- requête de contrôle avec table d'insertion
SELECT CAST(REPLACE(CLI_TEL, '.', '') as DECIMAL(20))
FROM   INSERTED
-- rollback en cas d'erreur
IF @@Error <> 0
ROLLBACK TRANSACTION

UPDATE T_CLIENT
SET    CLI_TEL = '01 02 03 04 05'
WHERE  CLI_ID = 1
Serveur : Msg 8114, Niveau 16, État 5, Procédure E_CLI_INS, Ligne 6
Erreur de conversion du type de données varchar en numeric.

UPDATE T_CLIENT
SET    CLI_TEL = '91.92.93.94.95'
WHERE  CLI_ID = 1

CREATE TRIGGER E_CLI_INS
       ON  CLIENT
       FOR INSERT, UPDATE
AS
-- inutile si pas d'update de la colonne visée
IF NOT UPDATE(CLI_TEL)
RETURN
-- requête de contrôle avec table d'insertion
SELECT CAST(REPLACE(CLI_TEL, '.', '') as DECIMAL(20))
FROM   INSERTED
-- rollback en cas d'erreur
IF @@Error <> 0
ROLLBACK TRANSACTION

CREATE TRIGGER E_CLI_INS
       ON  CLIENT
       FOR UPDATE
AS
-- inutile si pas d'update de la colonne visée
IF NOT UPDATE(CLI_TEL)
RETURN
-- requête de correction avec table d'insertion
UPDATE client
SET    cli_tel =
       REPLACE(REPLACE(I.CLI_TEL, ' ', '.'), '-', '.')
FROM   T_CLIENT C 
       INNER JOIN INSERTED I
             ON C.CLI_ID = I.CLI_ID
-- rollback en cas d'erreur
IF @@Error <> 0
ROLLBACK TRANSACTION

UPDATE T_CLIENT
SET    CLI_TEL = '88 77-66 55.44'
WHERE  CLI_ID = 1

cli_id      cli_nom                          Cli_tel 
----------- -------------------------------- -------------------- 
1           DUPONT                           88.77.66.55.44

CREATE TRIGGER E_CLI_INS
        ON  CLIENT
        FOR UPDATE
 AS
 -- inutile si pas d'update de la colonne visée
 IF NOT UPDATE(CLI_TEL)
 RETURN

 -- ouverture d'un curseur sur la table INSERTED
 -- pour les téléphones renseignés
 DECLARE CurIns CURSOR
 FOR
    SELECT CLI_ID, CLI_TEL
    FROM INSERTED
    WHERE CLI_TEL IS NOT NULL
 IF @@error <> 0 GOTO LBL_ERROR

 -- variable de travail 
 DECLARE @IdCli int, @TelAvant VARCHAR(20), @TelApres VARCHAR(20),
         @car CHAR(1), @i int, @j int

 -- ouverture du curseur
 OPEN CurIns
 IF @@error <> 0 GOTO LBL_ERROR

 -- lecture première ligne
 FETCH CurIns INTO @IdCli, @TelAvant

 -- boucle de lecture
 WHILE @@Fetch_Status = 0
 BEGIN
 -- si vide reboucle immédiatement
    IF @TelAvant = ''
    BEGIN
       FETCH CurIns INTO @IdCli, @TelAvant 
       CONTINUE
    END
 -- scrutation de la valeur du téléphone
    SET @i = 1
    SET @j = 0
    SET @TelApres = ''
 -- boucle de nettoyage sur tous les caractères
    WHILE @i <= LEN(@TelAvant)
    BEGIN
 -- reprise du caractère d'ordre i
       SET @car = SUBSTRING(@TelAvant,@i,1)
 -- on ne traite que les caractères de 0 à 9
       IF @car = '0' or @car = '1' or @Car = '2' or @Car = '3'
       or @car = '4' or @car = '5' or @Car = '6' or @Car = '7'
       or @car = '8' or @car = '9'
       BEGIN
          SET @TelApres = @TelApres + @Car
          SET @j = @j + 1
       END
       SET @i =@i + 1
    END
 -- si vide reboucle immédiatement
    IF @TelApres = ''
    BEGIN
       FETCH CurIns INTO @IdCli, @TelAvant 
       CONTINUE
    END
 -- découpage par tranche de 2 nombres
    SET @TelAvant = @TelApres
    SET @i = 1
    SET @TelApres = ''
 -- boucle de découpage
    WHILE @i <= LEN(@TelAvant)
    BEGIN
       SET @car = SUBSTRING(@TelAvant,@i,1)
       SET @TelApres = @TelApres + @Car
       IF @i % 2 = 0
          SET @TelApres = @TelApres + '-'
       SET @i =@i + 1
    END
 -- petit effet de bord si @TelApres se termine par un nombre pair, 
 -- alors tiret en trop !
    IF @j % 2 = 0 -- au pasage % est la fonction MODULO dans SQL Server
       SET @TelApres = SUBSTRING(@TelApres, 1, LEN(@TelApres)-1)
 -- mise à jour si différence
    IF @TelAvant <> @TelApres
    UPDATE CLIENT
    SET    CLI_TEL = @TelApres
    WHERE  CLI_ID = @IdCli 
    IF @@error <> 0 GOTO LBL_ERROR
    FETCH CurIns INTO @IdCli, @TelAvant
 END

 -- fermeture du curseur et désallocation de l'espace mémoire
 CLOSE CurIns
 DEALLOCATE CurIns

 RETURN

 -- rollback en cas d'erreur
 LBL_ERROR:
 ROLLBACK TRANSACTION

CREATE TRIGGER E_DEL_CLI ON T_CLIENT
FOR DELETE
AS

DELETE FROM T_FACTURE
FROM T_FACTURE F
     INNER JOIN DELETED D
           ON F.CLI_ID = D.CLI_ID
IF @@ERROR <> 0
   ROLLBACK TRANSACTION

CREATE TRIGGER E_AVI_INS ON T_AVION
FOR INSERT
AS
DECLARE @rowInUse int, @rows int

-- on regarde si les clefs existent bien dans la table T_VEHICULE
SELECT @RowInUse = COUNT(*)
FROM INSERTED
SELECT @Rows = COUNT(*)
FROM T_VEHICULE V 
     JOIN INSERTED I
          ON V.VHC_ID = I.VHC_ID
IF @RowInUse <> @Rows
BEGIN
   ROLLBACK
   RAISERROR ('Identifiant de l''héritant inexistant',16,1)
   RETURN
END

-- on regarde si les clefs n'existent pas dans la table T_BATEAU
SELECT @Rows = COUNT(*)
FROM T_BATEAU B
     JOIN INSERTED I
          ON B.VHC_ID = I.VHC_ID
IF @Rows <> 0
BEGIN
   ROLLBACK
   RAISERROR ('Fils pré existant dans l''entité soeur BATEAU',16,1)
END

CREATE TABLE T_VEHICULE
(VHC_ID INT)

CREATE TABLE T_AVION
(VHC_ID INT,
 AVI_MARQUE VARCHAR(16),
 AVI_MODELE VARCHAR(16))

CREATE TABLE T_BATEAU
(VHC_ID INT,
 BTO_NOM VARCHAR(16),
 BTO_PORT VARCHAR(16))

INSERT INTO T_VEHICULE VALUES (1)
INSERT INTO T_VEHICULE VALUES (2)
INSERT INTO T_VEHICULE VALUES (3)

INSERT INTO T_BATEAU VALUES (2, 'Penduick', 'Lorient')
INSERT INTO T_BATEAU VALUES (3, 'Titanic', 'Liverpool')
INSERT INTO T_AVION VALUES (1, 'Boeing', '747')

INSERT INTO T_AVION VALUES (3, 'Tupolev', '144')
INSERT INTO T_AVION VALUES (5, 'Airbus', 'A320')

NOT EXISTS(SELECT *
           FROM T_MOT MOT
                JOIN T_MOT_NOIR MNR 
                     ON MOT.MOT_MOT = MNR.MNR_MOT)

CREATE TRIGGER E_INS_MOT ON T_MOT
FOR INSERT
AS
IF EXISTS(SELECT * 
          FROM INSERTED I 
               JOIN T_MOT_NOIR M
                    ON I.MOT_MOT = M.MNR_MOT)
BEGIN
   ROLLBACK
   RAISERROR ('Insertion d''un mot noir impossible',16,1)
   RETURN
END

CREATE TABLE T_MOT 
(MOT_MOT CHAR(32),
 MOT_REF CHAR(8),
 MOT_PAGE INT,
 MOT_LIGNE INT,
 MOT_OFFSET INT)

CREATE TABLE T_MOT_NOIR
(MNR_MOT CHAR(32))

INSERT INTO T_MOT_NOIR VALUES ('LE')
INSERT INTO T_MOT_NOIR VALUES ('LA')
INSERT INTO T_MOT_NOIR VALUES ('LES')
INSERT INTO T_MOT_NOIR VALUES ('UN')
INSERT INTO T_MOT_NOIR VALUES ('UNE')
INSERT INTO T_MOT_NOIR VALUES ('DES')
INSERT INTO T_MOT_NOIR VALUES ('DE')

INSERT INTO T_MOT VALUES('LA',     'BIBLE', 147, 23, 14)
INSERT INTO T_MOT VALUES('VALLÉE', 'BIBLE', 147, 23, 14)
INSERT INTO T_MOT VALUES('DE',     'BIBLE', 147, 23, 14)
INSERT INTO T_MOT VALUES('LA',     'BIBLE', 147, 23, 14)
INSERT INTO T_MOT VALUES('MORT',   'BIBLE', 147, 23, 14)

CREATE TABLE T_PERSONNE_PRS
(PRS_ID      INT IDENTITY NOT NULL PRIMARY KEY,
 PRS_NOM     CHAR(32) NOT NULL,
 PRS_PRENOM  VARCHAR(16))

CREATE TABLE T_EMPLOYE_EMP
(PRS_ID        INT NOT NULL PRIMARY KEY  REFERENCES T_PERSONNE_PRS (PRS_ID),
 EMP_MATRICULE VARCHAR(8))

CREATE VIEW V_EMPLOYEE_EMP
AS
SELECT P.PRS_ID, P.PRS_NOM, P.PRS_PRENOM, E.EMP_MATRICULE
FROM   T_PERSONNE_PRS P
       INNER JOIN T_EMPLOYE_EMP E
             ON P.PRS_ID = E.PRS_ID

CREATE TRIGGER TRG_INS_EMPLOYE
ON V_EMPLOYEE_EMP
INSTEAD OF INSERT
AS
BEGIN
   INSERT INTO T_PERSONNE_PRS (PRS_NOM, PRS_PRENOM)
   SELECT PRS_NOM, PRS_PRENOM
   FROM   INSERTED

   INSERT INTO T_EMPLOYE_EMP (PRS_ID, EMP_MATRICULE)
   SELECT @@IDENTITY, EMP_MATRICULE
   FROM   INSERTED
END

INSERT INTO V_EMPLOYEE_EMP VALUES (1, 'DUPONT', 'Maurice', 'XF5090AZ')

SELECT * FROM T_PERSONNE_PRS

PRS_ID      PRS_NOM                          PRS_PRENOM      
----------- -------------------------------- ----------------
1           DUPONT                           Maurice

SELECT * FROM T_EMPLOYE_EMP

PRS_ID      EMP_MATRICULE
----------- -------------
1           XF5090AZ

CREATE TRIGGER E_DEV_UNIQUE_BORNE ON T_DEVELOPPEMENT_DEV 
FOR INSERT, UPDATE, DELETE 
AS

-- vérification de l'unicité de l'ensemble des bornes (bornes gauches et bornes droite)
IF EXISTS (SELECT COUNT(*), BORNE
            FROM   (SELECT DEV_BORNE_DROITE AS BORNE
                    FROM   T_DEVELOPPEMENT_DEV
                    UNION ALL
                    SELECT DEV_BORNE_GAUCHE  AS BORNE
                    FROM   T_DEVELOPPEMENT_DEV) T
            GROUP BY BORNE
            HAVING COUNT(*) <> 1)
ROLLBACK

-- vérification de la borne maximale comme étant deux fois le nombre de lignes de la table
IF (SELECT MAX(BORNE)
    FROM   (SELECT DEV_BORNE_DROITE AS BORNE
            FROM   T_DEVELOPPEMENT_DEV
            UNION ALL
            SELECT DEV_BORNE_GAUCHE  AS BORNE
            FROM   T_DEVELOPPEMENT_DEV) T) <> (SELECT COUNT(*) * 2 
                                             FROM   T_DEVELOPPEMENT_DEV)
BEGIN
   ROLLBACK
   RAISERROR ('Une borne dépasse la valeur maximale attendue', 16, 1) 
END

-- vérification de la borne minimale comme étant égale à un
IF (SELECT MIN(BORNE)
    FROM   (SELECT DEV_BORNE_DROITE AS BORNE
            FROM   T_DEVELOPPEMENT_DEV
            UNION ALL
            SELECT DEV_BORNE_GAUCHE  AS BORNE
            FROM   T_DEVELOPPEMENT_DEV) T) <> 1
BEGIN
   ROLLBACK
   RAISERROR ('Une borne dépasse la valeur minimale attendue', 16, 1) 
END

6. Cryptage du code, liaison au schema et recompilation

-- une table dont on ne désire pas que n'importe qui insére dedans
CREATE TABLE T_CRYPTEE_CRP
(CRP_ID   INT NOT NULL PRIMARY KEY,
 CRP_NOM  CHAR(32) NOT NULL,
 CRP_CONTROL VARBINARY(8))

-- un trigger qui vérifie la concordance entre le nom et le code de controle
CREATE TRIGGER TRG_CRP_INS_UPD
ON T_CRYPTEE_CRP
WITH ENCRYPTION
FOR INSERT, UPDATE
AS
   IF NOT EXISTS(SELECT *
                 FROM INSERTED
                 WHERE CRP_CONTROL = CAST(SUBSTRING(CRP_NOM, 1, 8) AS VARBINARY(8)))
      ROLLBACK

-- tentative d'insertion sans connaissance de l'algoritme de controle
INSERT INTO T_CRYPTEE_CRP VALUES (1, 'Dupont', CAST('        ' AS VARBINARY(8)))

SELECT * FROM T_CRYPTEE_CRP

CRP_ID      CRP_NOM                          CRP_CONTROL        
----------- -------------------------------- ------------------
 
-- aucun résultat l'insertion a été roolbacké par le trigger !

-- tentative d'insertion avec le code de controle
INSERT INTO T_CRYPTEE_CRP VALUES (1, 'Dupont', CAST('Dupont  ' AS VARBINARY(8)))

SELECT * FROM T_CRYPTEE_CRP

CRP_ID      CRP_NOM                          CRP_CONTROL       
----------- -------------------------------- ------------------
1           Dupont                           0x4475706F6E742020
-- l'insertion a bien eût lieu !

7. ANNEXE BIBLIOGRAPHIQUE