Simple thing would be imagine 2 tables, with 1 column.

Trigger on each table after the insert of a value into the column

Insert into Tab1 fires Trig1 which inserts the value into Tab2.

Tab2 trigger fires which inserts the value back to Tab1, which caused the trigger to fire on Tab1

And it goes on and on and on in an endless loop

http://msdn.microsoft.com/en-us/library/ms190739%28v=sql.100%29.aspx