· 7 years ago · Feb 27, 2019, 05:10 PM
1set nocount on
2IF OBJECT_ID('dbo.TestTable', 'U') IS NOT NULL
3 DROP TABLE dbo.TestTable;
4Create table dbo.TestTable (Id int, Col1 varchar(10), Col2 varchar(10), Col3 varchar(10))
5
6insert into dbo.TestTable (Id, Col1, col2, col3) values(1,'one','one','one')
7insert into dbo.TestTable (Id, Col1, col2, col3) values(1,'two','two','two')
8go
9create TRIGGER dbo.AfterUpdateTrigger ON dbo.TestTable
10AFTER UPDATE
11AS
12BEGIN
13 SET NOCOUNT ON;
14
15 SELECT * INTO #deleted FROM deleted
16 SELECT * INTO #inserted FROM inserted
17
18 ALTER TABLE #deleted DROP COLUMN col2
19 ALTER TABLE #inserted DROP COLUMN col2
20
21 select * from #deleted
22 except
23 select * from #inserted
24
25END
26GO
27
28update dbo.TestTable set col2 = 'three'
29update dbo.TestTable set Col1 = 'three'
30
31-- A table with an identity key and 48 other integer nullable columns
32CREATE TABLE dbo.Test
33(
34 ID integer IDENTITY PRIMARY KEY,
35 col01 integer NULL, col02 integer NULL, col03 integer NULL, col04 integer NULL,
36 col05 integer NULL, col06 integer NULL, col07 integer NULL, col08 integer NULL,
37 col09 integer NULL, col10 integer NULL, col11 integer NULL, col12 integer NULL,
38 col13 integer NULL, col14 integer NULL, col15 integer NULL, col16 integer NULL,
39 col17 integer NULL, col18 integer NULL, col19 integer NULL, col20 integer NULL,
40 col21 integer NULL, col22 integer NULL, col23 integer NULL, col24 integer NULL,
41 col25 integer NULL, col26 integer NULL, col27 integer NULL, col28 integer NULL,
42 col29 integer NULL, col30 integer NULL, col31 integer NULL, col32 integer NULL,
43 col33 integer NULL, col34 integer NULL, col35 integer NULL, col36 integer NULL,
44 col37 integer NULL, col38 integer NULL, col39 integer NULL, col40 integer NULL,
45 col41 integer NULL, col42 integer NULL, col43 integer NULL, col44 integer NULL,
46 col45 integer NULL, col46 integer NULL, col47 integer NULL, col48 integer NULL
47);
48
49-- A single row of sample data
50INSERT dbo.Test
51(
52 col01, col02, col03, col04, col05, col06, col07, col08, col09, col10,
53 col11, col12, col13, col14, col15, col16, col17, col18, col19, col20,
54 col21, col22, col23, col24, col25, col26, col27, col28, col29, col30,
55 col31, col32, col33, col34, col35, col36, col37, col38, col39, col40,
56 col41, col42, col43, col44, col45, col46, col47, col48
57)
58VALUES
59(
60 01, 02, 03, 04, 05, 06, 07, 08, 09, 10,
61 11, 12, 13, 14, 15, 16, 17, 18, 19, 20,
62 21, 22, 23, 24, 25, 26, 27, 28, 29, 30,
63 31, 32, 33, 34, 35, 36, 37, 38, 39, 40,
64 41, 42, 43, 44, 45, 46, 47, 48
65);
66
67CREATE TRIGGER Test_AU
68ON dbo.Test
69AFTER UPDATE
70AS
71BEGIN
72 IF @@ROWCOUNT = 0 RETURN;
73 SET ROWCOUNT 0;
74 SET NOCOUNT ON;
75
76 -- Zero @Mask to the same length as COLUMNS_UPDATED()
77 DECLARE @Mask varbinary(128) =
78 SUBSTRING(CONVERT(binary(128), 0x), 1, DATALENGTH(COLUMNS_UPDATED()));
79
80 DECLARE
81 @ColumnID integer, -- metadata ID of the target column
82 @Byte integer, -- byte of @Mask to change
83 @Work binary(1); -- working value of the byte to change
84
85 -- Find the object ID of the trigger's parent table
86 DECLARE @OID integer;
87 SELECT @OID = T.parent_id FROM sys.triggers AS T WHERE T.[object_id] = @@PROCID;
88
89 /* Set @Mask bits for column(s) of interest */
90
91 -- col30
92
93 -- Find byte, bit, and bit value to change
94 SET @ColumnID = COLUMNPROPERTY(@OID, N'col30', 'ColumnId') - 1;
95 SET @Byte = 1 + (@ColumnID / 8);
96
97 -- Retrieve the right @Mask byte and set the target bit
98 SET @Work = SUBSTRING(@Mask, @Byte, 1);
99 SET @Work = @Work | POWER(2, @ColumnID % 8);
100 SET @Mask = CONVERT(varbinary(128), STUFF(@Mask, @Byte, 1, @Work));
101
102 -- Test if the resulting mask matches COLUMNS_UPDATED
103 -- If not, other columns were updated
104 IF @Mask != COLUMNS_UPDATED() PRINT 'Other columns updated';
105END;
106
107UPDATE dbo.Test
108SET col30 = col30;
109
110UPDATE dbo.Test
111SET col30 = 1;
112
113UPDATE dbo.Test
114SET col30 = col30,
115 col31 = col31;
116
117UPDATE dbo.Test
118SET col30 = 2,
119 col31 = 3;
120
121UPDATE dbo.Test
122SET col31 = 4;
123
124Select st.name, sc.* From tempdb.sys.columns sc
125join sys.types st on sc.system_type_id=st.system_type_id AND sc.user_type_id=st.user_type_id
126Where object_id=OBJECT_ID('tempdb.dbo.#TempTableName');
127
128DROP FUNCTION IF EXISTS dbo.fn_OnlyColumnUpdated
129GO
130
131/*
132 Function that can be used to pass in a column number and the COLUMNS_UPDATED() bitmap (in a trigger)
133 to check if a particular column was the only one updated.
134 Usage example (in an update trigger):
135 -- check if MyDB.dbo.MyTable.MyColumn was only column in update statement*
136 IF MySharedDB.dbo.fn_OnlyColumnUpdated(COLUMNPROPERTY(OBJECT_ID('dbo.MyTable'), MyColumn', 'ColumnId'), COLUMNS_UPDATED()) = 1
137 RETURN
138*/
139CREATE FUNCTION dbo.fn_OnlyColumnUpdated
140(
141 @ColumnID INT, /* when used from within a trigger, use the Column_ID. When used from replication use the Column Ordinal number */
142 @ColumnUpdateBitmap VARBINARY(128)
143)
144RETURNS BIT
145AS
146BEGIN
147 /* if MyColumn only column updated don't do anything else */
148 DECLARE
149 @Mask varbinary(128),
150 @ColumnByte BINARY(1),
151 @ByteOrdinal INT,
152 @AppliedList VARBINARY(128),
153 @result BIT
154
155 SELECT
156 /* find the column ID for the AccessLevel column in this table */
157 /* build a varbinary variable of same length as COLUMNS_UPDATED() */
158 @Mask = SUBSTRING(CONVERT(binary(128), 0x), 1, DATALENGTH(@ColumnUpdateBitmap)),
159 /* Assign to a BINARY(1) the byte value representing that column being the only one used */
160 @ColumnByte = POWER(2, (@ColumnID - (((@ColumnID / 8)-1) * 8) -1)),
161 /* identify the array position holding the MyColumn Byte */
162 @ByteOrdinal = @ColumnID / 8,
163 /* now rebuild the varbinary representation of what columns are set (stuff the @ColumnByte into the right position in the array of bytes) */
164 @AppliedList = CONVERT(varbinary(128), STUFF(@Mask,@ByteOrdinal,1,@ColumnByte ))
165
166 SELECT
167 @result =
168 CASE WHEN @ColumnUpdateBitmap = @AppliedList THEN 1
169 ELSE 0
170 END
171 RETURN @result
172END
173GO
174
175CREATE TRIGGER TestMyTable_Upd
176ON dbo.MyTable
177AFTER UPDATE
178AS
179BEGIN
180 IF @@ROWCOUNT = 0 RETURN;
181 SET NOCOUNT ON;
182
183 IF MySharedDB.dbo.fn_OnlyColumnUpdated(COLUMNPROPERTY(OBJECT_ID('dbo.MyTable'), MyColumn', 'ColumnId'), COLUMNS_UPDATED()) = 1
184 BEGIN
185 PRINT 'Only MyColumn has been updated'
186 END
187 ELSE
188 BEGIN
189 PRINT 'Other columns have been updated'
190 END
191END;
192GO
193
194UPDATE dbo.MyTable
195SET MyColumn = 'test',
196 AnotherColumn = AnotherColumn
197WHERE ID = 1