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

Version imprimable

06/04/2011, 17h44
Oliv-

[sql2005][trigger] origine de déclenchement du trigger
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.
06/04/2011, 17h56
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 ?
06/04/2011, 17h59
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 ..

++
06/04/2011, 18h13
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.
06/04/2011, 18h16
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.

++
06/04/2011, 20h41
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 ?
07/04/2011, 07h47
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".
07/04/2011, 07h50
iberserk

Citation:

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?
07/04/2011, 09h23
Oliv-
Citation:

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 ?
07/04/2011, 09h59
iberserk
Citation:

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

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.

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

08/04/2011, 12h36
elsuket

Bonjour,

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

@++ ;)
08/04/2011, 14h00
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

Citation:

Envoyé par Oliv-

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

Code:

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

... ou aussi :

Code:

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

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

Code:

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

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

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' )
@++ ;)

Citation:

Envoyé par elsuket

En revanche je ne comprend pas pourquoi cela fonctionne :aie:

Bah... tant que ça fonctionne :mrgreen:

Citation:

Code:

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

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

Sauf si la colonne visée est mise à jour ET que c'est la seule
Citation:
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' )
@++ ;)
La doc précise :

Citation:

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 ?