complément d'information sur les triggers

**aldama** · 19/02/2009, 19h04

Au cours de la lecture d'un de vos tutoriels (petit guide de transact sql (V2000)
j'ai appris que le trigger before n'existe pas mais qu'on peut le similer.

"5.3.3. Annulation des effets d'un trigger
Pour empêcher un trigger de produire son effet on peut utiliser le ROLLBACK qui dans ce cas peut porter sur la transaction (ROLLBACK TRANSACTION celle qui a déclenchée le trigger par exemple) ou uniquement le trigger (ROLLBACK TRIGGER) c'est à dire sur les seuls effets de ce dernier.

C'est par ce biais que l'on peut simuler un trigger BEFORE : utiliser un trigger AFTER et le "rollbacker" ou bien utiliser un trigger INSTEAD et insérer quand même dans la table de destination.

je veux simuler un trigger before mais je sais pas comment m'y prendre malgré les instructions du tutoriel.

comment s'y prendre prendre pour inserrer automatiquement une clé primaire qui est sous forme varchar à chaque nouvel enrégistrement?
merci d'avance
où puis-je avoir des informations complémentaires sur les triggers?

**elsuket** · 19/02/2009, 20h31

Bonjour,

comment s'y prendre prendre pour inserrer automatiquement une clé primaire qui est sous forme varchar à chaque nouvel enrégistrement?

Si vous nous donniez le DDL de votre table et ce que vous souhaitez faire exactement, nous pourrions vous aider avec précision ...
Avoir une clé primaire de type VARCHAR est une mauvaise idée ...

@++

**jero44** · 19/02/2009, 20h44

Oui il est possible d'écrire un trigger instead of pour valoriser une colonne PK, par exemple:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
 
create table test(c1 char(5) constraint pk_test primary key, c2 char(5));
go
create trigger tr_iof_insert_test on test instead of insert as
begin
  insert into test(c1, c2)
    select c2, c2 from inserted;
end;
go    
insert into test(c2) values('b');
select * from test;

Pour obtenir plus d'information par rapports aux déclencheurs rien ne remplace la documentation officielle: http://msdn.microsoft.com/fr-fr/library/ms189799.aspx
Enfin petite précision le trigger instead of ne permet pas de simuler un trigger before (c'est bien sur faux pour les trigger after)

**aldama** · 19/02/2009, 23h06

ok, j'ai créé une table t_immeuble:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
create table T_IMMEUBLE (code_immeuble varchar (20) primary key, denom_immeuble varchar (50), nbre_etage_immeuble smallint, code_prop varchar foreign key references T_PROPRIETAIRE 
(code_prop), code_quart varchar (20) foreign key references T_QUARTIER (code_quart))

je veux que code_immeuble soit renseigné sous la forme: "2009-00001"
où 2009 est l'année en cours et 00001 s'incremente automatiquement à chaque enrégistrement.

j'ai donc créé mon trigger de la sorte:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
 
create trigger trig_immeuble
on T_immeuble
instead of insert
 
as
 
declare @n varchar(10)
declare @m varchar(10)
 
 
 
set @n= (select count (code_immeuble) from T_immeuble)
set @m =(select max (code_immeuble) from t_immeuble)
 
 
if substring(@m, 1 , 4) = convert(char(4),CURRENT_TIMESTAMP,121 )
      set @n = @n+1
else
set @n=1
 
 
           if @n <=9
              insert into code_immeuble values (convert(char(4), current_timestamp,121)
               + '/'+ '0000'+@N)
 
 
           if @n > 9
              insert into code_immeuble values (convert(char(4), current_timestamp,121)
              + '/'+ '000'+@N)
 
 
           If @n>99 
              insert into code_immeuble values (convert(char(4),current_timestamp, 121)
              + '/'+ '00' + @n)
 
 
           if @n>999
              insert into code_immeuble values (convert(char(4), current_timestamp,121) 
              + '/'+ '0'+@N)
 
 
           if @n>9999
              insert into code_immeuble values (convert(char(4), current_timestamp,121)
              + '/'+ @N)

lorsque je veux entrer un nouvel enrégistrement sous la forme:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
insert into T_immeuble ( denom_immeuble, nbre_etage_immeuble,
code_prop, code_quart)
values ( 'ARDOISE', 4, 7,8)

je recois un message d'erreur qui me dit que code_immeuble ne peut pas etre null.
je veux donc transformer mon trigger en trigger before pour permettre un nouvel enregistrement dans ma table sans renseigner le champ code_immeuble.

je précise que c'est juste un test et que c'est pour m'exercer.

merci d'avance

**elsuket** · 19/02/2009, 23h49

Essayez donc :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
CREATE TRIGGER trig_immeuble
	ON T_immeuble
INSTEAD OF INSERT
AS
BEGIN
	DECLARE @n INT,
			@m INT,
			@annee INT,
			@val VARCHAR(10)
 
	SELECT @n = COUNT(*),
			@m = MAX(CAST(code_immeuble AS INT)),
			@annee = YEAR(GETDATE())
	FROM dbo.T_immeuble  
 
	IF SUBSTRING(CAST(@m AS VARCHAR), 1 , 4) = @annee
	BEGIN
		 SET @n = @n + 1
	END
	ELSE
	BEGIN
		SET @n = 1
	END
 
	SELECT @val = @annee + '/' + REPLICATE('0', 5 - LEN(CAST(@n AS VARCHAR)) + @n
 
	INSERT INTO dbo.T_immeuble
	(
		code_immeuble,
		denom_immeuble,
		nbre_etage_immeuble,
		code_prop,
		code_quart
	)
	SELECT @val,
			denom_immeuble,
			nbre_etage_immeuble,
			code_prop,
			code_quart
	FROM INSERTED
END

Sachez que vous avez demandez à SQL Server d'effectuer pour vous des transtypages implicites (entiers en chaîne et inversement), et que cela engendre un surcoût au niveau des performances.
Le code que je vous propose fait explicitement ces transtypages (CAST) mais ce n'est pas non plus très propre.

Lorsque vous devez utiliser la fonction COUNT, préférez lui * plutôt qu'une colonne de la table : de même qu'avec le prédicat EXISTS, il est optimisé pour trouver la colonne la plus sélective sur laquelle effectuer le comptage.
Ce sont les seuls cas où il est propre d'utiliser * (ne l'utilisez pas dans un SELECT que vous mettrez en production : cela oblige SQL Server à rechercher dans les tables système le nom des colonnes de la table ...)

Qualifiez le nom de vos objets (tables, procédures, fonctions, vues, ...) avec celui du schéma auquel ils appartiennent: cela évite à SQL Server de rechercher dans quel schéma votre objet se trouve, et de comparer le propriétaire demandant l'exécution avec celui de la l'objet.

Une clé primaire qui n'est pas numérique ou bien de type DATETIME horodatée n'est pas une bonne idée.
En plus votre clé primaire est composite : toute valeur d'une clé primaire doit être atomique : votre numéro de sécurité sociale peut constituer une clé primaire car celui-ci vous identifie directement.
Ce n'est pas le cas des immeubles : leur date de création est une de leurs propriétés comme le nombre de leurs étages, mais en aucun ces deux propriétés permettent d'identifier directement un immeuble dans votre base de données.

Lorsque vous écrivez des triggers, pensez toujours à utiliser les tables virtuelles INSERTED et DELETED, qui ont strictement la même structure que la table cible de la mise à jour de données, mais qui contiennent :
- pour INSERTED, les valeurs qui vont être appliquées à la table
- pour DELETED, les valeurs qui étaient présentes dans la table avant la mise à jour de données

Voilà une autre raison d'avoir une clé primaire dont les valeurs sont strictement atomiques : vous pouvez alors réaliser des jointures entres ces deux tables virtuelles et la table cible de la mise à jour de données pour implémenter votre contrainte métier complexe dans un trigger.

Enfin retenez qu'un traitement par trigger est plus coûteux qu'un traitement en procédure stockée.

@++

**aldama** · 20/02/2009, 10h32

merci pour ton aide. j'ai pas encore tester le code, je le ferai tout a l'heure.
Mille merci pour le cours qui va avec, j'en aurai bien besoin.
comme je le disais, je suis en phase d'apprentissage, et je me lance des defits a atteindre.

lorsque je maitriserai un peu les triggers, je passerai au procedures stockées.
mais deja, est-ce qu'il m'était possible de remplacer ce trigger par une procedure stockée?

**elsuket** · 20/02/2009, 10h45

Bonjour,

Un trigger n'est qu'un type de procédure stockée, mais qui a la propriété de s'éxécuter lors de la survenance d'un événement sur un table.
Il ne renvoie pas de résultats et ne prend pas de paramètres.

Tput dépend de ce que vous souhaitez faire : si vous avez beaucoup de procédures stockées qui vont insérer des données dans la table, il est intéressant d'avoir un trigger car cela évite d'avoir à répéter le code dans toutes les procédures stockées.
Une autre approche est d'écrire une procédure stockée qui est dédiée à l'insertion d'un immeuble, et de l'appeler dans toutes les procédures stockées qui doivent ajouter un nouvel immeuble.
Mais dans ce cas, vous devez penser à appeler celle-ci dans les procédures stockées, ce qui est facile quand on un ou deux développeurs, au-delà :

@++

**aldama** · 20/02/2009, 21h23

je crois avoir suivit tes instructions mais je recois un message d'erreur lors de la création du trigger, a savoir " syntaxe incorecte vers le mot clé 'insert'".

je vois qu'après le insert into ......, on a mis select (......) au lieu de values (.....). Est-ce que c'est pas la cause du problème?

**aldama** · 20/02/2009, 22h08

j'ai essayé de tester l'exemple donné ds cette discussion a savoir

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
CREATE TABLE test(c1 char(5) constraint pk_test PRIMARY KEY, c2 char(5));
go
CREATE TRIGGER tr_iof_insert_test ON test instead of INSERT AS
begin
  INSERT INTO test(c1, c2)
    SELECT c2, c2 FROM inserted;
end;
go

si la création du trigger ne pose pas de problème, l'insertion de donnée dans la base test en pose.
lorsque je fais

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

INSERT INTO test(c2) VALUES('b');

je recois un message d'erreur:
"La colonne 'c1' de la table 'test' ne peut pas être NULL."

cela m'amène a poser deux questions:
1- pourquoi INSERT into.... ne ramène pas de message d'erreur ici?
2- pourkoi l'insertion n'est toujours pas possible?

merci

**elsuket** · 21/02/2009, 21h50

Bonsoir,

je crois avoir suivit tes instructions mais je recois un message d'erreur lors de la création du trigger, a savoir " syntaxe incorecte vers le mot clé 'insert'".

Effectivement je viens de m'apercevoir que j'ai fait plusieurs erreurs dans cette commande, qui devient :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT @val = @annee + '/' + REPLICATE('0', 4 - LEN(CAST(@n AS VARCHAR))) + CAST(@n AS VARCHAR)

Au départ, j'ai oublié une parenthèse pour la fonction REPLICATE, j'ai mis 5 au lieu de 4, et je n'ai pas transtypé @n en VARCHAR pour qu'il soit "compatible" avec @val

je vois qu'après le insert into ......, on a mis select (......) au lieu de values (.....). Est-ce que c'est pas la cause du problème?

On peut spécifier une clause SELECT pour un ordre INSERT, tant que celui-ci retourne un ensemble de données ayant le même ordre de colonnes que celui spécifié, et tant que les types des colonnes retournées par le SELECT est le même que celui de la table cible de l'INSERT.
Attention : il n'y a pas de notion d'ordre dans une table d'une base de données : vous pouvez placer les colonnes dans l'ordre que vous souhaitez, c'est toujours le même tuple qui est défini.

La différence avec VALUES, c'est qu'avec cette dernière clause, vous ne pouvez insérer exactement qu'une seule ligne, alors qu'avec le SELECT vous pouvez en insérer de 0 à n (suivant ce que l'instruction SELECT retourne, qui peut être un ensemble vide ou bien un ensemble de cardinal n

)

"La colonne 'c1' de la table 'test' ne peut pas être NULL."

Cela signifie que vous avez défini votre colonne c1 comme obligatoirement valuée, et peut-être comme clé primaire.
C'est pourquoi vous devez fournir une valeur pour cette colonne.
Ce n'aurait pas été le cas dans les mêmes conditions si vous aviez spécifié c1 de type entier avec la propriété de compteur auto-incrémenté (IDENTITY).
Cela répond à vos deux dernières questions

@++

**aldama** · 23/02/2009, 11h02

merci pour toutes les précisions que tu as fais.

le problème est que je suis à la recherche d'une syntaxe pré-insert à partir de after insert ou de instead of.

Pour inserrer des données dans une table auto-increment, l'on n'a pas besoin de créer un trigger.

par contre je veux simuler l'auto-increment de la clé de ma table avec un trigger pre-insert.

comment simuler un trigger pré-insert?

Dans l'exemple que tu as donné, c1 est un primary key et ne s'incremente pas automatiquement

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
CREATE TABLE test(c1 char(5) constraint pk_test PRIMARY KEY, c2 char(5));
go
CREATE TRIGGER tr_iof_insert_test ON test instead of INSERT AS
begin
  INSERT INTO test(c1, c2)
    SELECT c2, c2 FROM inserted;
end;
go

complément d'information sur les triggers

MS SQL Server

Discussions similaires

Partager

Partager