Discussion :

[aldama]

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]

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]

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]

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]

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]

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?