>Hi everybody,
>
>I wrote the following trigger that updates the table itself among other tasks. Suprisingly it works and doesn't go into the infinite loop. Can someone please explain how does it work?
>
>

>create trigger SR_tr_UPDATE on Student_Registration after update
>as
>  begin
>     declare @Status char(1)
>     select @Status = Status from Inserted
>     if @Status = 'D'
>        begin
>        update Student_Registration set Drop_Date = getdate()
>        from Student_Registration SR, Inserted I
>        where SR.SID = I.SID and SR.TermID = I.TermID
>
>        update Student_Summary set NumOfCourses = NumOfCourses - 1
>        from Student_Summary SS inner join Inserted I on
>        SS.SID = I.SID
>
>        update Course_Summary set NumberOfStudents = NumberOfStudents - 1
>        from Inserted I, Course_Term CT, Course_Summary CS
>        where  I.TermID = CT.TermID and (CS.cYear = CT.cYear and CS.cID = CT.cID)
>       end
>   else
>    begin
>      print 'Invalid Status'
>      rollback tran
>   end
>end

>
>Thanks a lot in advance.

Check Recursive Triggers under CREATE TRIGGER topic in BOL:

Recursive Triggers
SQL Server also allows for recursive invocation of triggers when the RECURSIVE_TRIGGERS setting is enabled using ALTER DATABASE.
Recursive triggers enable the following types of recursion to occur:
Indirect recursion
With indirect recursion, an application updates table T1. This fires trigger TR1, updating table T2. In this scenario, trigger T2 then fires and updates table T1.

Direct recursion
With direct recursion, the application updates table T1. This fires trigger TR1, updating table T1. Because table T1 was updated, trigger TR1 fires again, and so on.

The following example uses both indirect and direct trigger recursion Assume that two update triggers, TR1 and TR2, are defined on table T1. Trigger TR1 updates table T1 recursively. An UPDATE statement executes each TR1 and TR2 one time. Additionally, the execution of TR1 triggers the execution of TR1 (recursively) and TR2. The inserted and deleted tables for a specific trigger contain rows that correspond only to the UPDATE statement that invoked the trigger.

Note:
The previous behavior occurs only if the RECURSIVE_TRIGGERS setting is enabled by using ALTER DATABASE. There is no defined order in which multiple triggers defined for a specific event are executed. Each trigger should be self-contained.



Disabling the RECURSIVE_TRIGGERS setting only prevents direct recursions. To disable indirect recursion also, set the nested triggers server option to 0 by using sp_configure.

If any one of the triggers performs a ROLLBACK TRANSACTION, regardless of the nesting level, no more triggers are executed.