Update in a trigger

[apollo11]

Can I update a column for the same table in a trigger,within the trigger, for which the trigger belongs?

E.g.:

CREATE TRIGGER [MainTrigger] ON [SimpleBuyStatus]
FOR INSERT,UPDATE
AS

BEGIN
Declare @POID integer,
@Status varchar(10),
@Action varchar(10),
@DocType varchar(10),
@Result varchar(10),
@BorgId int,
@ReqName varchar(255),
@ReqNum varchar(35),
@PONum varchar(35),
@EmailId varchar(255),
@MsgBody varchar(2000)

SET NOCOUNT ON

set @POID=-1
-- Indicate the Dispatcher to pick up the PO's which have successfully passed the Encumbrance
Select @POID = i.DocID,@BorgId=i.BorgId,@DocType = i.DocType,@Status = i.Status,@Action = i.Action, @Result = i.Result from inserted i


if (@POID > 0 and @DocType='PO' and @Action='ENCUMBR' and @Status='SMBUYRDY' and @Result='SUCCESS')
BEGIN
Update PO set POStatusID=3 where POID=@POID
Update POItem set POStatusID=1 where POID=@POID
Update ReqPO set StatusCode=1 where POID=@POID
Update ReqPOItem set StatusCode=1 where POID=@POID
Update SimpleBuyStatus set Status='BUYPRCSD', Result='SUCCESS' where DocType='PO' and DocID=@POID

Look at the statement in BOLD

 

[Cagliostro]

The bold statement fire the execution of your trigger. As a result is a recursive trigger(and infinite also). John Fill
ivfmd@mail.md

 

[apollo11]

Does that mean that I have to turn the Recursive triggers option for the database on? This trigger runs fine even with the option off, so I am wondering what is happening here?

 

[Cagliostro]

Maybe it stops after the bold instructions if this option is set off. If you allow recursive triggers means they indirectly will call themselves. John Fill
ivfmd@mail.md

 

[tlbroadbent]

A trigger does not call itself recursively unless the Recursive Triggers database option is set. Terry

http://members.home.net/tlbroadbent/index.htm