Salut à tous, j'ai essayé de créer un trigger de journalisation... je m'explique, lors d'un update, je veux enregistrer dans une table

Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
 
create table tcd_history(
	ID integer primary key identity,
	who varchar(64),
	action varchar(20),
	field varchar(200) default null,
	date datetime,
	old_value varchar(500) default null,
	entity varchar(200),
	pk_name varchar(500),
	pk_value varchar(650)
);
les modifs qui lui sont faites.

J'ai beaucoup de table, et je ne veux pas faire un trigger spécifique à chaque table, je veux quelque chose de modulaire...

Pour le moment, j'ai ça :


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
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
 
CREATE trigger trigger_test on dbo.une_table
for update
as
begin
 
	declare @user varchar(150);
	declare @entity varchar(200);
	declare @action varchar(30);
	declare @columnName varchar(100);
	declare @columnType varchar(100);
	declare @strListPKeys varchar(700);
	declare @strListPKeysVal varchar(700);
	declare @countUpdated integer;
 
	set @countUpdated=(select count(*) from Inserted);
 
	if @countUpdated=1
	begin
 
		select * into inserted_table from Inserted;
		select * into deleted_table from Deleted;
 
		set @entity=(select object_name(parent_obj) from sysobjects where id=@@PROCID);
 
		declare myCursor cursor local for
		SELECT C.COLUMN_NAME, C.DATA_TYPE
		FROM   INFORMATION_SCHEMA.COLUMNS C
		       INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
		             ON     C.TABLE_CATALOG = TC.TABLE_CATALOG
		                AND C.TABLE_SCHEMA  = TC.TABLE_SCHEMA
		                AND C.TABLE_NAME    = TC.TABLE_NAME
		       INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU
		             ON     TC.CONSTRAINT_CATALOG = CCU.CONSTRAINT_CATALOG
		                AND TC.CONSTRAINT_SCHEMA  = CCU.CONSTRAINT_SCHEMA
		                AND TC.CONSTRAINT_NAME    = CCU.CONSTRAINT_NAME
		                AND C.COLUMN_NAME         = CCU.COLUMN_NAME
		WHERE  TC.TABLE_SCHEMA    = 'dbo'
		  AND  TC.TABLE_NAME      = @entity
		  AND  TC.CONSTRAINT_TYPE = 'PRIMARY KEY';
 
		open myCursor;
		fetch next from myCursor into @columnName,@columnType;
		if @@fetch_status = 0
		begin
			set @strListPKeys=@columnName+'['+@columnType+']';
			declare @req varchar(300);
			set @req='select '+@columnName+' from inserted_table';
 
 
			declare @cursor int;
			exec sp_cursoropen @cursor OUTPUT,@req,2,8193;
			exec sp_cursoroption @cursor,2,'myTempCursor';
			declare @x cursor;
			exec sp_describe_cursor @x out,N'global','myTempCursor';
			fetch next from @x;
			declare @val sql_variant;
			fetch next from myTempCursor into @val;
			set @strListPKeysVal=rtrim(convert(char(150),@val));
			close myTempCursor;
			deallocate myTempCursor;
			close @x;
			deallocate @x;
		end
		fetch next from myCursor into @columnName,@columnType;
		while @@fetch_status = 0
		begin
			--char 254
			set @strListPKeys=@strListPKeys+'£'+@columnName+'['+@columnType+']';
			set @req='select '+@columnName+' from inserted_table';
 
			exec sp_cursoropen @cursor OUTPUT,@req,2,8193;
			exec sp_cursoroption @cursor,2,'myTempCursor';
			exec sp_describe_cursor @x out,N'global','myTempCursor';
			fetch next from @x;
			fetch next from myTempCursor into @val;
 
			set @strListPKeysVal=@strListPKeysVal+'£'+rtrim(convert(char(150),@val));
			close myTempCursor;
			deallocate myTempCursor;
			close @x;
			deallocate @x;
 
			fetch next from myCursor into @columnName, @columnType;
		end
		close myCursor;
		deallocate myCursor;
 
		set @user=(select current_user);
		set @action='update';
 
 
 
		declare cursorCols cursor local for SELECT COLUMN_NAME
		FROM INFORMATION_SCHEMA.COLUMNS
		WHERE TABLE_NAME=@entity;
 
		open cursorCols;
 
		declare @modified integer;
 
		fetch next from cursorCols into @columnName;
		while @@fetch_status=0
		begin
			exec('select count(*) as modif into get_modif_table from inserted_table i, deleted_table d where i.'+@columnName+'<>d.'+@columnName);
			set @modified=(select modif from get_modif_table);
			if @modified=1
			begin
 
				set @req='select '+@columnName+' from deleted_table';
 
				exec sp_cursoropen @cursor OUTPUT,@req,2,8193;
				exec sp_cursoroption @cursor,2,'myTempCursor';
				exec sp_describe_cursor @x out,N'global','myTempCursor';
				fetch next from @x;
				fetch next from myTempCursor into @val;
				close myTempCursor;
				deallocate myTempCursor;
				close @x;
				deallocate @x;
 
				insert into tcd_history(who,action,field,date,old_value,entity,pk_name,pk_value) values(@user,@action,@columnName,getdate(),rtrim(convert(varchar,@val)),@entity,@strListPKeys,@strListPKeysVal);
			end
			drop table get_modif_table;
 
			fetch next from cursorCols into @columnName;
		end
		close cursorCols;
 
 
		drop table inserted_table;
		drop table deleted_table;
	end
end

Mon problème est que d'une part je ne voudrais pas que la procedure retourne les lignes pointées dans les curseurs internes au trigger (est-ce possible ?) et d'autre part c'est un peu lent (meilleures idées ??).

Pourriez-vous m'aider à optimiser mon trigger
Grand merci