[SQL2005]Trigger On delete, insert, update(colonne)

Version imprimable

1 pièce(s) jointe(s)

Bonjour à tous,

Est-il possible de faire un trigger en mettant un nom de champ entre parentheses pour un UPDATE (comme sur Oracle).

Exemple :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 if exists (select name from sysobjects WHERE name='trig_update_nbre_produits' AND type='TR') DROP TRIGGER trig_update_nbre_produits GO CREATE TRIGGER trig_update_nbre_produits ON produit FOR INSERT, DELETE, UPDATE(id_cat) AS BEGIN UPDATE categorie SET categorie.nombre_produits = (SELECT COUNT(p.id_prod) FROM produit p WHERE p.id_cat = categorie.id_cat); PRINT 'Exécution du trigger de MAJ : Actualisation de la colonne nombre_produits effectuée sur la table categorie' ; END GO
Voici le diagramme de données de ma base. J'ai rajouté la colonne 'nombre_produits' dans la table 'categorie'.

Merci beaucoup !

PS : Le diagramme de données généré par Enterprise Manager est en fichier attaché.

Possible comme ceci :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TRIGGER trig_update_nbre_produits
ON produit FOR INSERT, DELETE, UPDATE
AS
BEGIN	
   IF UPDATE(id_cat)
   BEGIN     
	UPDATE categorie 
	SET categorie.nombre_produits =
		(SELECT COUNT(p.id_prod) FROM produit p
			WHERE p.id_cat = categorie.id_cat)
 	PRINT 'Exécution du trigger de MAJ : Actualisation de la colonne nombre_produits effectuée sur la table categorie' ;
   END
END
GO

Cepandant considérez l'utilisation des tables DELETED et INSERTED. Comme vous n'avez pas indiqué ce que fait ce trigger difficile de vous aider plus en avant.

A +

25/02/2007, 22h07
slim

Merci pour la réponse.

Le trigger permet la mise à jour de la colonne 'nombre_produits' dans la table 'categorie', lors d'un delete, insert ou update (uniquement la colonne 'id_cat' dans la table 'produit') sur la table produit.

Ne faut-il pas faire plutot comme ca ?

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
if exists (select name from sysobjects 
WHERE name='trig_update_nbre_produits' 
AND type='TR')
DROP TRIGGER trig_update_nbre_produits
GO
CREATE TRIGGER trig_update_nbre_produits
ON produit FOR INSERT, DELETE, UPDATE
AS
if update(id_cat)
	UPDATE categorie 
	SET categorie.nombre_produits =
		(SELECT COUNT(p.id_prod) FROM produit p
			WHERE p.id_cat = categorie.id_cat);
 	PRINT 'Exécution du trigger de MAJ : Actualisation de la colonne nombre_produits effectuée sur la table categorie' ;
 
BEGIN	
	UPDATE categorie 
	SET categorie.nombre_produits =
		(SELECT COUNT(p.id_prod) FROM produit p
			WHERE p.id_cat = categorie.id_cat);
 	PRINT 'Exécution du trigger de MAJ : Actualisation de la colonne nombre_produits effectuée sur la table categorie' ;
END
GO

Parce que sinon, le code n'est exécuté que pour le UPDATE non ?

Et que m'apporterait l'utilisation des tables inserted et deleted dans ce cas ?

26/02/2007, 16h32
SQLpro

UPDATE() est une fonction. A ne pas confondre avec UPDATE matAble qui est un ordre SQL...

Quant à votre code il est faux pour plusieurs raisonj :
1) la syntaxe du IF n'admet qu'une seule instruction
2) vous n'utilisez toujours pas les tables inserted et deleted.

Postez le DDL de vos tables ainsi qu'un jeu d'essais afin que l'on vous aide.
En cela vous respecterez la charte de postage :
http://www.developpez.net/forums/showthread.php?t=96701

A +

Bonsoir,

Voici la DDL des deux tables concernées (je pensais que le diagramme de données suffisait...) :

Table 'produit' :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
CREATE TABLE [dbo].[produit](
	[id_prod] [char](15) NOT NULL,
	[id_cat] [char](5) NULL,
	[titre_prod] [varchar](30) NULL,
	[detail_prod] [varchar](30) NULL,
	[image_prod] [varchar](30) NULL,
	[nouv_prod] [bit] NULL,
	[prom_prod] [bit] NULL,
	[sel_prod] [bit] NULL,
	[poids_prod] [tinyint] NULL,
	[dispo_prod] [bit] NULL,
	[delai_prod] [varchar](30) NULL,
	[prixht_prod] [decimal](8, 2) NULL,
	[prixhtprom_prod] [decimal](8, 2) NULL,
	[tauxtva_prod] [decimal](4, 2) NULL,
 CONSTRAINT [pk_produit] PRIMARY KEY CLUSTERED 
(
	[id_prod] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Table 'categorie' :

Code:

1
2
3
4
5
6
7
8
9
CREATE TABLE [dbo].[categorie](
	[Id_cat] [char](5) NOT NULL,
	[Nom_cat] [varchar](30) NULL,
	[nombre_produits] [int] NULL,
 CONSTRAINT [pk_cat] PRIMARY KEY CLUSTERED 
(
	[Id_cat] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Je ne vois toujours pas l'utilité d'utiliser les tables inserted et deleted pour ce que je veux. Il suffit de mettre un update (instruction SQL) sur la colonne 'nombre_produits' de la table 'categorie'.

Merci beaucoup.

Je ferais donc 3 triggers pour éviter de TOUT reclaculer car votre trigger au fur et à mesure de la montée en charge va prendre de plus en plus de temps...

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_update_nbre_produits
ON produit FOR UPDATE
AS
BEGIN	
   COMMIT
   IF UPDATE(id_cat)
	UPDATE categorie 
	SET nombre_produits = 
		(SELECT COUNT(p.id_prod) 
                 FROM produit p
		 WHERE p.id_cat = categorie.id_cat)
END
GO
 
 
CREATE TRIGGER trig_update_nbre_produits
ON produit FOR INSERT
AS
BEGIN	
   COMMIT
   UPDATE categorie 
   SET nombre_produits = nombre_produits +
		(SELECT COUNT(p.id_prod) 
                 FROM   inserted i
		 WHERE  i.id_cat = categorie.id_cat)
END
GO
 
 
CREATE TRIGGER trig_update_nbre_produits
ON produit FOR INSERT
AS
BEGIN	
   COMMIT
   UPDATE categorie 
   SET nombre_produits = nombre_produits -
		(SELECT COUNT(p.id_prod) 
                 FROM   deleted d
		 WHERE  d.id_cat = categorie.id_cat)
END
GO

A +

Ok, Merci.

Pourquoi faut-il mettre un commit avant l'execution de l'update dans le trigger ?
Ca marche sans. Et je crois que le commit, doit etre mis à la fin ?

J'ai apporté quelques petites corrections aux triggers que voici :

Trigger ON INSERT :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
if exists (select name from sysobjects 
where name='trig_insert_nbre_produits' 
AND type='TR')
drop trigger trig_insert_nbre_produits
GO
create trigger trig_insert_nbre_produits
on produit for insert
as
begin
	update categorie 
	set categorie.nombre_produits = categorie.nombre_produits + 
		   (select count(i.id_prod)
			from inserted i
			where i.id_cat = categorie.id_cat);
 	PRINT 'Exécution du trigger for insert : Actualisation de la colonne nombre_produits effectuée sur la table categorie' ;
end
GO

Trigger ON UPDATE :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
if exists (select name from sysobjects 
where name='trig_update_nbre_produits' 
AND type='TR')
drop trigger trig_update_nbre_produits
GO
create trigger trig_update_nbre_produits
on produit for update
as
begin
	if update(id_cat)
	update categorie 
	set nombre_produits = 
		   (select count(p.id_prod)
			from produit p
			where p.id_cat = categorie.id_cat);
 	PRINT 'Exécution du trigger for update : Actualisation de la colonne nombre_produits effectuée sur la table categorie' ;
END
GO

Trigger ON DELETE :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
if exists (select name from sysobjects 
where name='trig_delete_nbre_produits' 
AND type='TR')
drop trigger trig_delete_nbre_produits
GO
create trigger trig_delete_nbre_produits
on produit for delete
as
begin
	update categorie 
	set categorie.nombre_produits = categorie.nombre_produits -
		   (select count(d.id_prod)
			from deleted d
			where d.id_cat = categorie.id_cat);
 	PRINT 'Exécution du trigger for delete : Actualisation de la colonne nombre_produits effectuée sur la table categorie' ;
END
GO

Et ca marche sans probleme.

Merci.

27/02/2007, 16h36
SQLpro

Plus vous validerez tôt la transaction et moins elle durera. Moins elle durera et moins vous aurez de blocage...

En particulier pour l'update !

A +