September 16, 2008 at 11:42 pm
I m using SQL server 2005, i want to apply a trigger after insert, update that will update a tinyint column "IsUpdated" to 1
but when it will update that column will that trigger be fired again.I mean my custom application will read
that column and will pick only updated records then it will mark that column zero again after getting data
but that trigger will update them again .....
how can i control that?
Create TRIGGER [dbo].[IsUpdated]
ON [dbo].[CorporateOwners]
AFTER INSERT, UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @CorporateOwnerID int
select @CorporateOwnerID = CorporateOwnerID from inserted
-- Insert statements for trigger here
update CorporateOwners
set IsUpdated = 1
where CorporateOwners.CorporateOwnerID = @CorporateOwnerID
END
And one of my other windows application will use following statment
update CorporateOwners
set IsUpdated = 0
where IsUpdated = 1
kindly reply me asap, its very urgent.
Thanx in Advance.
September 17, 2008 at 12:45 am
There is a "nested triggers" server configuration option that allows/disallows triggers to act recursively.
If you don't want to change the server option: Change your logic using an if statement so that you update the IsUpdated to 1 column only if the IsUpdated is 0. So you change it only once per row and the trigger is fired only once.
September 17, 2008 at 1:12 am
Hi there,
No offense but why not just use a stored procedure?
I'm not that familiar with triggers because I don't use them. Heard that there were issues in them and I think its particullarly on the control of data... like what happend...
Just use a stored procedure
_____________________________________________
[font="Comic Sans MS"]Quatrei Quorizawa[/font]
:):D:P;):w00t::cool::hehe:
MABUHAY PHILIPPINES!
September 17, 2008 at 5:39 am
select * from sys.configurations where configuration_id = 116
--------------------------
its maximum col should be 1 to AVOID recursion
----i guess it will help you 🙂
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply