Table A triggers an insert onto Table B, Table B triggers an insert onto Table A, what happens?
Aside from the obvious "uh, don't do that"... this took about two minutes to test.

USE tempdb;
GO

CREATE TABLE dbo.TableA(ID INT);

CREATE TABLE dbo.TableB(ID INT);

GO
CREATE TRIGGER dbo.TableAToB
ON dbo.TableA
FOR INSERT
AS
  INSERT dbo.TableB SELECT ID FROM inserted;
GO
CREATE TRIGGER dbo.TableBToA
ON dbo.TableB
FOR INSERT
AS
  INSERT dbo.TableA SELECT ID FROM inserted;
GO

INSERT dbo.TableA(ID) VALUES(1);


Error:


  Msg 217, Level 16, State 1, Procedure TableBToA
  Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).


Also, no row is ever inserted into either table.