Trigger conunderum

Question

Trigger conunderum

douglas.allison-1136597

SSC-Addicted

Points: 444
More actions
July 16, 2010 at 2:53 am

#224857

I have 2 simple tables tst_a and tst_a_arch
On tsts_a I have this trigger
----------------------------------------------------------
create TRIGGER [dbo].[updated]
ON [dbo].[tst_a]
AFTER UPDATE
AS
if (columns_Updated() & 14) > 0
Begin
insert into tst_a_arch (test1,test2) values ('a','b')
end
GO
-----------------------------------------------------------
If I run the insert comand as stand alone bit of T-SQL I can add as many rows into tst_a_arch with those values as I want
However when I try to run this as a trigger I get the following error
'The row value(s) updated or deleted either do not make the row unique or they alter multiple rows(2 rows)'
Thanks for any pointers

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 1

douglas.allison-1136597 (7/16/2010)
I have 2 simple tables tst_a and tst_a_arch
On tsts_a I have this trigger
----------------------------------------------------------
create TRIGGER [dbo].[updated]
ON [dbo].[tst_a]
AFTER UPDATE
AS
if (columns_Updated() & 14) > 0
Begin
insert into tst_a_arch (test1,test2) values ('a','b')
end
GO
-----------------------------------------------------------
If I run the insert comand as stand alone bit of T-SQL I can add as many rows into tst_a_arch with those values as I want
However when I try to run this as a trigger I get the following error
'The row value(s) updated or deleted either do not make the row unique or they alter multiple rows(2 rows)'

Something is off.

"& 14" means columns_updated() function is testing for columns #2, #3 and #4 getting updated but table appears to have only two columns.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.