recursive trigger?

[luckroth]

hi all. i want a trigger that can do in loop. for example:

it is recursive trigger.
---------------------------------------------------------------------------------------
CREATE TRIGGER UpdateStudent On tblStudent FOR Update as
DECLARE @studentid int

SELECT @studentid=studentReg FROM tblStudent WHERE Status=2

UPDATE tblStudent SET Status=2 WHERE StudentID=@StudentID

UPDATE tblStudent SET Status=0 WHERE StudentReg=@StudentID
-----------------------------------------------------------------------------------------

if in tblstudent still have status =2, this trigger will never end.
could anyone help me please?

[jp140768]

What are you trying to accomplish? If you want to update the specific student that was originally updated, you should be getting the student id from table Inserted.

[luckroth]

What are you trying to accomplish? If you want to update the specific student that was originally updated, you should be getting the student id from table Inserted.

many thank for your reply. but it is not my target. i want a trigger that can do in loop. so more please?

[jp140768]

What are you trying to do? The code example you gave, updates the status twice. I need to understand what you want to do, and why you want to do it, before being able to give you better advice.

My set up of SQL server, by default does not allow recursive triggers, to allow them, right click on the database in Enterprise Manager, go to the options tab, and click on the box.