[sql2005][trigger] origine de déclenchement du trigger

[Oliv-]

Bonjour,
J'ai 2 tables l'une FL_Risque contient notamment un code "formule" et l'autre fl_garantiesrisque les lignes de garanties correspondantes à cette formule.
Le trigger FL_Risque

Code :

1
2
3
ALTER TRIGGER updFl_Risque ON dbo.FL_Risque
FOR UPDATE
AS...

vient ajouter dans fl_garantiesrisque les lignes de la formule si la formule entre deleted et inserted est différente.

Par contre je peux choisir d'insérer, modifier ou supprimer directement depuis la table fl_garantiesrisque et dans ce cas je veux juste mettre à jour (par un trigger) le code "formule" dans FL_Risque mais sans déclencher le trigger de celle-ci.

Comment faire ? peux t'on savoir quel événement à déclenché le trigger ?

j'utilise

Code :

1
2
3
4
IF @@NESTLEVEL =4
begin
...
end

et

Code :

ALTER TABLE fl_risque DISABLE TRIGGER ALL

mais ce n'est pas efficace car c'est à l'intérieur d'un programme et ne suis pas toujours sûr du niveau car il peut y avoir dans le trigger des update pour corriger des valeurs.

et le disable trigger semble perturber le programme.

[Oliv-]

Question complémentaire, quand je fais un

Code :

ALTER TABLE fl_risque DISABLE TRIGGER ALL

dans ma procédure stockée, cela désactive les trigger de cette table pour tout le monde ou juste mon instance ?

[mikedavem]

Bonjour,

Pourquoi ne pas ajouter une colonne suppémentaire dans la table FL_Risque nommée par exemple origine avec deux valeurs possibles : 0 ou 1 ou les noms des table d'origine ('FL_Risque' et 'fl_garantiesrisque') avec par défaut la valeur 'FL_Risque'.

Il suffit ensuite de se baser sur cette valeur pour déclencher ou non votre premier trigger.

L'idée de désactiver le trigger est valable pour toute la table et peut poser des soucis dans l'intégrité de vos données ..

++

[Oliv-]

Merci pour cette idée mais je n'ai pas la possibilité de modifier cette table, dont la définition est faite par l'éditeur du progiciel.

[mikedavem]

L'ajout d'une colonne à une table ne devrait en principe poser aucun problème (règle no9 de Codd) si l'application est bien modélisée.

++

[Oliv-]

J'ai déjà eu le cas lors d'une mise à jour, la colonne avait disparu après.
Peut on connaitre le declencheur du trigger ?

[iberserk]

Évidemment, c'est le problème des montées de version des progiciel, rien ne vous empêche de garder un script à passer après chaque mise à jour de votre progiciel permettant de revenir à votre modélisation "maison".

[iberserk]

ALTER TABLE fl_risque DISABLE TRIGGER ALL

Cà c'est clairement à éviter, les TRIGGERS sont là pour assurer la cohérence de votre modèle/données.

Pouvez vous modifier le TRIGGER que "vous ne voulez pas déclencher"?
Si c'est le cas testez si vous pouvez le modifier afin qu'il ne fasse rien dans le cas de la seule mise à jour de la colonne formule?

Le TRIGGER n'est pas là pour rien par définition, pouquoi ne voulez vous pas q'uil se déclenche?

[Oliv-]

Envoyé par iberserk

Pouvez vous modifier le TRIGGER que "vous ne voulez pas déclencher"?
Si c'est le cas testez si vous pouvez le modifier afin qu'il ne fasse rien dans le cas de la seule mise à jour de la colonne formule?

Merci je vais effectivement m'orientier vers cette solution, donc je peux faire un

Code :

1
2
IF UPDATE(formule_ID)
RETURN

quelle est la syntaxe pour vérifier que c'est la seule colonne modifiée ?

[iberserk]

quelle est la syntaxe pour vérifier que c'est la seule colonne modifiée ?

Quelque chose comme çà:

Code :

1
2
IF UPDATE(formule_ID) AND NOT UPDATE(colonneA)...
RETURN

[Oliv-]

J'ai trouvé et adapté un code de Piotr Rodak

il faut créer une table

Code :

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE dbo.tTally  (NUMBER INTEGER);
 
DECLARE @n INT
SET @n =1
WHILE @n <= 255
  BEGIN
      INSERT INTO ttally
                  (NUMBER)
      VALUES      (@n)
      SET @n=@n + 1
  END

pour l'exemple voici une table

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
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
IF EXISTS(SELECT 1 FROM sys.TABLES WHERE object_id = object_id('[dbo].[tManyColumns]'))
	DROP TABLE [dbo].[tManyColumns]
GO
CREATE TABLE [dbo].[tManyColumns]
(	[c1] [int] NOT NULL,
	[c2] [int] NOT NULL,
	[c3] [int] NULL,
	[c4] [int] NULL,
	[c5] [int] NULL,
	[c6] [int] NULL,
	[c7] [int] NULL,
	[c8] [int] NULL,
	[c9] [int] NULL,
	[c10] [int] NULL,
	[c11] [int] NULL,
	[c12] [int] NULL,
	[c13] [int] NULL,
	[c14] [int] NULL,
	[c15] [int] NULL,
	[c16] [int] NULL,
	[c17] [int] NULL,
	[c18] [int] NULL,
	[c19] [int] NULL,
	[c20] [int] NULL,
	[c21] [int] NULL,
	[c22] [int] NULL,
	[c23] [int] NULL,
	[c24] [int] NULL,
	[c25] [int] NULL,
	[c26] [int] NULL,
	[c27] [int] NULL,
	[c28] [int] NULL,
	[c29] [int] NULL,
	[c30] [int] NULL,
	[c31] [int] NULL,
	[c32] [int] NULL,
	[c33] [int] NULL,
	[c34] [int] NULL,
	[c35] [int] NULL,
	[c36] [int] NULL,
	[c37] [int] NULL,
	[c38] [int] NULL,
	[c39] [int] NULL
 constraint [PK1] PRIMARY KEY clustered 
(
	[c1] ASC,
	[c2] ASC
)
) ON [PRIMARY]
 
go
--populate the table
 
SET NOCOUNT ON;
SET XACT_ABORT ON;
GO
 
BEGIN TRANSACTION;
INSERT INTO [dbo].[tManyColumns]([c1], [c2], [c3], [c4], [c5], [c6], [c7],
 [c8], [c9], [c10], [c12], [c13], [c14], [c15], [c16], [c17], [c18], [c19])
SELECT 1, 3, 32, 14, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 
NULL, NULL, 88, NULL, NULL UNION ALL
SELECT 1, 4, 32, 14, NULL, NULL, NULL, NULL, NULL, 1, NULL, NULL, NULL, 
NULL, NULL, 88, NULL, NULL UNION ALL
SELECT 2, 1, 32, 14, NULL, 1, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
 NULL, 11, 88, NULL, NULL
COMMIT;
RAISERROR (N'[dbo].[tManyColumns]: Insert Batch: 1.....Done!', 10, 1) WITH NOWAIT;
 
GO
--check contents of the table
SELECT * FROM dbo.tManyColumns

ensuite le trigger

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
IF EXISTS(SELECT 1 FROM sys.triggers WHERE parent_id = object_id('[dbo].[tManyColumns]')
			AND name = 'trg1')
	DROP TRIGGER trg1
 
go
 
CREATE TRIGGER trg1 ON [dbo].[tManyColumns] 
FOR INSERT, UPDATE, DELETE 
AS 
begin
	declare @nb_colonne_maj int
	declare @table_id int 
	SELECT @table_id = parent_id FROM sys.triggers WHERE object_id = @@procid 
 
	--select columns_updated() [columns_updated string]
 
	;WITH column_updated_bytesCTE --divide bitmask into bytes. this query requires number table Admin.tTally in the database
	AS
	(
			SELECT number ByteNumber, convert(BINARY(1),
			substring(columns_updated(), number, 1)) [ByteValue]
			FROM dbo.tTally
			WHERE number <= datalength(columns_updated())
	),
	columnsCTE AS --return columns belonging to table @TableObjectId, calculate appropriate bit masks
	(
			SELECT column_id, [name] column_name, ByteNumber, ByteValue,
							power(2, (((a.column_id - 1 ) % 8) + 1) - 1) BitMask
			FROM sys.COLUMNS a INNER JOIN
					column_updated_bytesCTE b
							ON ((a.column_id - 1) / 8) + 1 = b.ByteNumber
			WHERE a.object_id = @table_id
	) 
--	select *, 
--		case when ByteValue & BitMask > 0 then 'yes' else 'no' end [Is column updated?] from columnsCTE
 
 
	SELECT @nb_colonne_maj=count(*) FROM columnsCTE WHERE ByteValue & BitMask > 0 
 
	SELECT @nb_colonne_maj
end
 
go

et le test

Code :

1
2
 
UPDATE dbo.tManyColumns SET c10 = 1 ,c35 = 35 WHERE c1 = 1 AND c2 = 4

j'obtiens le nombre de colonnes mises à jour. dans le Code originel on obtient la liste des colonnes et si elles ont été mise à jour.

[Oliv-]

Alors du coup je me dit que je vais la convertir en fonction mais le résultat est différent une idée ?

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
---source http://sqlblogcasts.com/blogs/piotr_rodak/archive/2010/04/28/columns-updated.aspx
---dbo.Ncolumns_updated (@@procid,columns_updated())
 
CREATE FUNCTION dbo.fnNcolumns_updated (@procid int ,@Vbcolumns_updated varbinary (32))
returns int
AS 
begin
	declare @nb_colonne_maj int
	declare @table_id int 
	SET @nb_colonne_maj=0
	SELECT @table_id = parent_id FROM sys.triggers WHERE object_id = @procid 
 
	--select columns_updated() [columns_updated string]
 
	;WITH column_updated_bytesCTE --divide bitmask into bytes. this query requires number table Admin.tTally in the database
	AS
	(
			SELECT number ByteNumber, convert(BINARY(1),
			substring(@Vbcolumns_updated, number, 1)) [ByteValue]
			FROM dbo.tTally
			WHERE number <= datalength(@Vbcolumns_updated)
	),
	columnsCTE AS --return columns belonging to table @TableObjectId, calculate appropriate bit masks
	(
			SELECT column_id, [name] column_name, ByteNumber, ByteValue,
							power(2, (((a.column_id - 1 ) % 8) + 1) - 1) BitMask
			FROM sys.COLUMNS a INNER JOIN
					column_updated_bytesCTE b
							ON ((a.column_id - 1) / 8) + 1 = b.ByteNumber
			WHERE a.object_id = @table_id
	) 
--	select *, 
--		case when ByteValue & BitMask > 0 then 'yes' else 'no' end [Is column updated?] from columnsCTE
 
 
	SELECT @nb_colonne_maj=count(*) FROM columnsCTE WHERE ByteValue & BitMask > 0 
 
	RETURN( @nb_colonne_maj);
end
 
go

c'était ma variable varbinary où il manquait la taille (32) car 254 colonnes max

[elsuket]

Bonjour,

Un peu lourd tout ça, alors que la fonction COLUMNS_UPDATED() est là pour ça ...

@++

[Oliv-]

J'ai bien étudié cette fonction, si tu as une façon de l'utiliser plus efficace je suis preneur.
Je veux savoir si ma colonne flor_formule_id a été mise à jour et si c'est la seule ?
ce qui me donne au final

Code :

1
2
IF UPDATE(flor_formule_id) AND (SELECT dbo.fnNcolumns_updated (@@procid,columns_updated()))=1
RETURN

ps :dans mon cas il y a 50 colonnes dans ma table

[aieeeuuuuu]

Envoyé par Oliv-

Je veux savoir si ma colonne flor_formule_id a été mise à jour et si c'est la seule ?

Code SQL :

1
2
3
4
5
6
7
8
9
10
11
12
 
	IF COLUMNS_UPDATED() = (
			SELECT POWER(2,
						COLUMNPROPERTY(
							OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME),
							COLUMN_NAME, 'ColumnID'
						) - 1
					)
			FROM INFORMATION_SCHEMA.COLUMNS
			WHERE TABLE_NAME = 'NomTable'
				AND COLUMN_NAME = 'flor_formule_id'
	)

[aieeeuuuuu]

... ou aussi :

Code SQL :

1
2
3
4
5
6
7
8
9
 
IF COLUMNS_UPDATED() = ( SELECT COLUMNS_UPDATED() & COLUMNPROPERTY(
			OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME),
			COLUMN_NAME, 'ColumnID'
			) 
		FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME = 'NomTable'
			AND COLUMN_NAME = 'NomColonne'
	)

[elsuket]

Désolé de revenir tardivement sur ce sujet.

Pour moi la première solution ne marche pas, mais la seconde fonctionne parfaitement.

En revanche je ne comprend pas pourquoi cela fonctionne
Pour moi :

Code :

COLUMNS_UPDATED() = ( SELECT COLUMNS_UPDATED() & COLUMNPROPERTY(

Les deux membres de l'égalité retournent des valeurs différentes

On peut m'expliquer ?

Sinon, on peut aussi écrire la condition comme suit :

Code :

1
2
3
4
5
6
7
8
9
10
IF COLUMNS_UPDATED() =
(
	SELECT		COLUMNS_UPDATED() & (C.column_id - 1)
	FROM		sys.COLUMNS AS C
	INNER JOIN	sys.TABLES AS T ON C.object_id = T.object_id
	INNER JOIN	sys.schemas AS S ON T.schema_id = S.schema_id
	WHERE		S.name = 'nomSchema'
	AND		T.name = 'nomTable'
	AND		C.name = 'nomColonne'
)

Ou si l'on veut rester dans la norme :

Code :

1
2
3
4
5
6
7
8
9
IF COLUMNS_UPDATED() =
(
	SELECT	COLUMNS_UPDATED() & (ORDINAL_POSITION - 1)
	FROM	INFORMATION_SCHEMA.COLUMNS
	WHERE	TABLE_CATALOG = DB_NAME()
	AND	TABLE_SCHEMA = SCHEMA_NAME()
	AND	TABLE_NAME = 'nomTable'
	AND	COLUMN_NAME = 'nomColonne'
)

@++

[aieeeuuuuu]

Envoyé par elsuket

En revanche je ne comprend pas pourquoi cela fonctionne

Bah... tant que ça fonctionne

Code :

COLUMNS_UPDATED() = ( SELECT COLUMNS_UPDATED() & COLUMNPROPERTY(

Les deux membres de l'égalité retournent des valeurs différentes

Sauf si la colonne visée est mise à jour ET que c'est la seule

Ou si l'on veut rester dans la norme :

Code :

@++

La doc précise :

Caution:
In SQL Server 2005, the ORDINAL_POSITION column of the INFORMATION_SCHEMA.COLUMNS view is not compatible with the bit pattern of columns returned by COLUMNS_UPDATED. To obtain a bit pattern compatible with COLUMNS_UPDATED, reference the ColumnID property of the COLUMNPROPERTY system function when you query the INFORMATION_SCHEMA.COLUMNS view, as shown in the following example:

sans donner plus d'info...

Pourtant ta solution est la même, à l'exception de la méthode de récupération de l'ID de colonne....

Je ne comprend pas non plus pourquoi la première solution en fonctionne pas chez toi... peux tu poster tes scripts de test afin que je le lance chez moi ?