Discussion :

[marcolo21]

Bonjour tous le monde,

j'aurai besoin d'aide pour un trigger. Ce trigger a pour but de noté les modifications faite dans un table dans un autre table. C'est donc un trigger d'audit d'une table.

Chaque fois que quelqu'un modifie une table, le trigger note dans la table audit (qui est identique a l'autre table) l'utilisateur qui a modifier ou ajouter quelque chose dans la table et elle note quelle colonne a été modifier et la date de la modification.

voila le code que j'ai de fait:

Le code du trigger:

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
 
 
 
ALTER  TRIGGER Trigger4
ON dbo.test_table1
AFTER INSERT, UPDATE, DELETE
AS 
BEGIN
 
DECLARE @TotalString as VARCHAR(50)
DECLARE @ColumnsUpdated as VARBINARY(1000)
SET @ColumnsUpdated = COLUMNS_UPDATED()
 
 
select @TotalString = master.dbo.SYSTEM_LogUpdateTriggerChanges
						('test_table1','dbo',@ColumnsUpdated)
 
INSERT INTO test_table2
( userid, value1, value2, value3, columnsUpdated, COLUMNS_UPDATED_Value)
SELECT USER_NAME(), value1, value2, value3, @totalstring, @ColumnsUpdated 
FROM INSERTED
END

Le code de la fonction SYSTEM_LogUpdateTriggerChanges:

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
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
 
 
ALTER  FUNCTION SYSTEM_LogUpdateTriggerChanges
	(
	@Table VARCHAR(100),@Schema VARCHAR(100),@ColumnsUpdated AS VARBINARY(500)
	)
RETURNS VARCHAR(1000)
AS
BEGIN
 
DECLARE @FldsUpdated AS VARCHAR(2000)
 
DECLARE @columnName AS VARCHAR(255)
DECLARE @tableName AS VARCHAR(255)
DECLARE @tableSchema AS VARCHAR(255)
DECLARE @hasChanged AS INT
Declare @Totalstring AS VARCHAR(1000)
DECLARE @NomTable as varchar(100)
 
set @NomTable = @Schema + '.' + @Table
 
DECLARE curTableColumns CURSOR FOR
               SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
                                FROM INFORMATION_SCHEMA.COLUMNS
                               WHERE TABLE_NAME = @NomTable
 
SET @totalstring = ''
SET @FldsUpdated = ''
 
OPEN curTableColumns
FETCH NEXT FROM curTableColumns INTO @tableSchema, @tableName, @columnName
 
 
WHILE @@FETCH_STATUS = 0 
BEGIN
               SELECT @hasChanged = master.dbo.SYSTEM_IsBitSetInBitmask
                                (
                                @ColumnsUpdated,
                        (SELECT colid 
                        FROM syscolumns 
                        WHERE id = OBJECT_ID(@tableSchema + '.' + @tableName)
                        AND NAME = @columnName)
 
                                )
 
 
 
            IF @hasChanged <> 0
            BEGIN
 
                 SET @FldsUpdated = @columnName
 
                 SET @totalstring = @totalstring + @FldsUpdated + ','
 
 
 
     END
           FETCH NEXT FROM curTableColumns INTO @tableSchema, @tableName, @columnName
END
CLOSE curTableColumns
DEALLOCATE curTableColumns
 
RETURN @TotalString
END

Le code de la fontion SYSTEM_IsBitSetInBitmask (fonction qui est a l'intérieur de ma fonction):

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
 
 
ALTER function SYSTEM_IsBitSetInBitmask 
(@bitmask varbinary(500), @colid int)  
    returns int  
as  
begin  
    declare @word smallint  
    declare @bit  smallint  
    declare @mask binary(2)  
    declare @mval int  
    declare @oldword binary(2)    
 
    if @colid < 1 return 0  
 
    SELECT @word = 1 + FLOOR((@colid -1)/16)  
 
    SELECT @bit = (@colid -1) % 16  
 
    SELECT @mval = POWER(2, @bit)  
    SELECT @mask = convert( binary(2), unicode( substring( convert( nchar(2), convert( binary(4), @mval ) ), 2, 1 ) ) )  
 
    SELECT @oldword = convert( binary(2), SUBSTRING( convert( nvarchar(64),@bitmask), @word, 1) )  
    IF @oldword IS NULL return 0  
 
    return  convert( smallint, @oldword ) & convert( smallint, @mask )  
end

Je tien a préciser que je suis en SQL Server 2000

La fonction SYSTEM_IsBitSetInBitmask est une fonction prit de sql server 2005, j'ai seulement copier le code de la fonction sys.fn_IsBitSetInBitmask de sql server 2005.


Mon problème est que ma fonction SYSTEM_LogUpdateTriggerChanges retourne toujours rien alors qu'elle serait suposer retourné les colones qui on été modifier. Si je sort le code de la fonction il marche alors je ne comprend pas pourquoi il ne marche pas dans la fonction

Merci beaucoup d'avance pour votre aide

[elsuket]

Bonjour,

Les fonctions ne sont pas très performantes sous SQL Server.
Si on y ajoute des curseurs, ça n'aide pas.

Ne serait-il pas plus simple d'écrire ?

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
ALTER TRIGGER TR_A_IUD_test_table1
	ON dbo.test_table1
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
	INSERT	INTO dbo.test_table2
	(
		userid
		, old_col_1
		, old_col_2
		, old_col_3
		, new_col_1
		, new_col_2
		, new_col_3
	)
	SELECT		USER_NAME()
			, D.col_1
			, D.col_2
			, D.col_3
			, I.col_1
			, I.col_2
			, I.col_3
	FROM		inserted AS I
	FULL OUTER JOIN	deleted AS D
END

@++

[marcolo21]

merci pour ta réponse,

ce serai plus simple de faire sa oui, mais je désire que la colone qui a été modifié soit marqué dans une colone appeler columnsupdated.

Je veux faire ma fonction pour quelle soit utilisable sur plusieurs trigger, donc j'aimerai qu'elle universel si je peux dire.

ainsi j'aurai seulement a passé les paramètres souhaiter et de faire un insert dans ma table et j'aurai la colone qui a été modifier. Comme sa ce serai beaucoup plus simple pour faire plusieur trigger qui fonctionne de la même manière.

merci

[elsuket]

Dans ce cas ne vaudrait-il mieux pas générer avec du code SQL dynamique la structure de la table d'audit ainsi que le trigger ?

@++

[SQLpro]

D'autre part vous pouvez utiliser les opérateurs hexadécimaux & | ^ plutôt que d'invoquer les puissances de 2 !!!

A +

[marcolo21]

salut elsuket,

je ne comprend pas ce que vous voulez dire par

"Dans ce cas ne vaudrait-il mieux pas générer avec du code SQL dynamique la structure de la table d'audit ainsi que le trigger"

désoler je ne suis encore qu'un simple débutant en sql alors je ne comprend pas beaucoup ce que vous voulez dire par la.

Pour SQLpro,

quand vous parler de puissances de 2 voulez vous parler de mes varbinary ? Car la fonction columns_updated retourne un varbinary que je passe a la fonction que j'ai récupérer de sql 2005 pour quelle me retourne la ou les colones qui on été modifier.

merci

[elsuket]

Vous pouvez générer le script de la table en vous inspirant ce celui que j'ai publié ici, en ajoutant vos colonnes (user et date), et en faisant de même pour le trigger

@++

[marcolo21]

Désoler mais je ne comprend pas du tout votre script,

c'est trop avancer pour moi.

Il n'y aurait pas de solution plus simple ?

merci