complément d'information sur les triggers

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?

Bonjour,

Citation:

---

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

@++ ;)

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

Code:

---

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)

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

Code:

---

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:

---

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:

---

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

Essayez donc :

Code:

---

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.

@++ ;)

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?

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à : :aie:

@++ ;)

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?

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

Code:

---

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:

---

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

Bonsoir,

Citation:

---

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:

---

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 :aie:

Citation:

---

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

Citation:

---

"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 ;)

@++ ;)

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:

---

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

---