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 |
Optimisation trigger journalisation
Répondre avec citation
Partager