July 22, 2002 at 2:53 pm
Hi,
I have a trigger that I want to fire when the eight (8) column is updated. BOL suggest to use COLUMN_UPDATED and the bit mask for determinig which columns were updated (8th column = (power(2,(8-1))=128).
The trigger does not fire when I use COLUMNS_UPDATED(). It works fine without it. Any idea why?
Here is the code:
ALTER TRIGGER [tr_FinalDateChnge] ON [dbo].[tblData]
FOR UPDATE
AS
SET NOCOUNT ON
DECLARE @SDG int
IF (COLUMNS_UPDATED() & 128) > 0
Begin
DECLARE StepSDG_Cursor CURSOR FOR
SELECT i.SDG
FROM tblData td inner join inserted i on td.SDG=i.SDG
inner join Deleted d on d.SDG=i.SDG
where i.FinalDateMod != d.FinalDateMod
OPEN StepSDG_Cursor
FETCH NEXT FROM StepSDG_Cursor
INTO @SDG
WHILE @@FETCH_STATUS = 0
BEGIN
--SELECT @SDG=SDG FROM inserted
If @SDG IS NOT NULL
Exec zp_lkEmailEnum @SDG
FETCH NEXT FROM StepSDG_Cursor
INTO @SDG
END
CLOSE StepSDG_Cursor
DEALLOCATE StepSDG_Cursor
End
Thanks,
Jakub
July 23, 2002 at 2:43 am
I tested using a bitwise OR operator and it seemed to work so try: -
if columns_updated() | 128 = 128
Regards,
Andy Jones
.
July 23, 2002 at 9:30 am
Nope,
That did not work either.
This is plain vanilla SQL installation. It should work just fine, what is wrong with it?
Jakub
July 24, 2002 at 2:20 am
This is the test I ran, give this a go: -
--Create table
create table tab
(
c1 int
, c2 int
, c3 int
, c4 int
, c5 int
, c6 int
, c7 int
, c8 int
)
GO
--Create trigger
create trigger tg_tab_up on tab for update as
begin
if columns_updated() | 128 = 128
begin
select 1
end
end
GO
--Insert record
insert into tab
values
(
1
, 2
, 3
, 4
, 5
, 6
, 7
, 8
)
--Perform updates
update tab set c1 = 5
update tab set c2 = 5
update tab set c3 = 5
update tab set c4 = 5
update tab set c5 = 5
update tab set c6 = 5
update tab set c7 = 5
update tab set c8 = 5
--Expceted results
-- (1 row(s) affected)
--
--
-- (1 row(s) affected)
--
--
-- (1 row(s) affected)
--
--
-- (1 row(s) affected)
--
--
-- (1 row(s) affected)
--
--
-- (1 row(s) affected)
--
--
-- (1 row(s) affected)
--
--
-- -----------
-- 1
--
-- (1 row(s) affected)
--
--
-- (1 row(s) affected)
Regards,
Andy Jones
.
July 24, 2002 at 6:59 am
I'll bet your table consists of more than 8 columns, doesn't it? I first reported this as a bug back with MSSQL7, but when MSSQL2000 came out, it turned out to just be a documentation error. The following is from the 2000 documentation on CREATE TRIGGER:
-- begin snippet --
IF (COLUMNS_UPDATED())
Tests, in an INSERT or UPDATE trigger only, whether the mentioned column or columns were inserted or updated. COLUMNS_UPDATED returns a varbinary bit pattern that indicates which columns in the table were inserted or updated.
The COLUMNS_UPDATED function returns the bits in order from left to right, with the least significant bit being the leftmost. The leftmost bit represents the first column in the table; the next bit to the right represents the second column, and so on. COLUMNS_UPDATED returns multiple bytes if the table on which the trigger is created contains more than 8 columns, with the least significant byte being the leftmost.
-- end snippet --
Note the last sentence, which is not in the MSSQL7 documentation. Also see example F in the same topic (which is also not in the MSSQL7 documentation), but note that it is in error as it says to use the given method only if you need to test columns beyond the first 8. It should say to use the given method if your table contains more than 8 columns, regardless of whether you're testing any columns beyond 8.
So, here is Andy's example modified to demonstrate the correct way to test a table with more than 8 columns:
--Create table
create table tab
(
c1 int
, c2 int
, c3 int
, c4 int
, c5 int
, c6 int
, c7 int
, c8 int
, c9 int
)
GO
--Create trigger
create trigger tg_tab_up on tab for update as
begin
if SUBSTRING(COLUMNS_UPDATED(),1,1) = power(2,(8-1))
begin
select columns_updated() as BitValue,
deleted.c8 as C8_OldValue, inserted.c8 as C8_NewValue,
deleted.c9 as C9_OldValue, inserted.c9 as C9_NewValue
from inserted, deleted
end
if SUBSTRING(COLUMNS_UPDATED(),2,1) = power(2,(1-1))
begin
select columns_updated() as BitValue,
deleted.c8 as C8_OldValue, inserted.c8 as C8_NewValue,
deleted.c9 as C9_OldValue, inserted.c9 as C9_NewValue
from inserted, deleted
end
end
GO
--Insert record
insert into tab
values
(
1
, 2
, 3
, 4
, 5
, 6
, 7
, 8
, 9
)
--Perform updates
update tab set c1 = 11
update tab set c2 = 12
update tab set c3 = 13
update tab set c4 = 14
update tab set c5 = 15
update tab set c6 = 16
update tab set c7 = 17
update tab set c8 = 18
update tab set c9 = 19
Note that the Columns_Updated() return value is now two bytes long, but in reverse order (least significant byte first).
Jay Madren
Jay Madren
July 24, 2002 at 10:03 am
Yup,
that was it. My table has way more that 8 fields. I followed the BOL which is evidently wrong.
Thanks for info, Jay. I thought that I was going crazy.
Again, thanks to all that helped 🙂
Jakub
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply