Trigger state manipulation - Handle with care

Occasionaly someone has a brainstorm and they think of a solution technique to a problem without thinking outside their problem to the surrounding environment. In other cases they consider the complete environment and decide to use the solution technique in a creative way knowing its limitations and its benefits. For the second situation it is of value to know that you can switch triggers off and on thus:-

CREATE TABLE TestTable
(
Col1 INT IDENTITY
)
GO

CREATE TRIGGER TestTrigger ON TestTable
FOR INSERT
AS
ROLLBACK TRANSACTION
GO

CREATE PROCEDURE InsertRow
AS
EXEC SwitchOffTrigger
INSERT INTO TestTable DEFAULT VALUES
EXEC SwitchOnTrigger
GO

CREATE PROCEDURE SwitchOffTrigger
AS
DISABLE TRIGGER DBO.TestTrigger ON DBO.TestTable
GO

CREATE PROCEDURE SwitchOnTrigger
AS
ENABLE TRIGGER DBO.TestTrigger ON DBO.TestTable
GO

INSERT INTO TestTable DEFAULT VALUES
INSERT INTO TestTable DEFAULT VALUES
INSERT INTO TestTable DEFAULT VALUES
GO

EXEC InsertRow
EXEC InsertRow
EXEC InsertRow
GO

SELECT * FROM TestTable
GO

Of course I would never recommend using a rollback inside a trigger in a real world situation but it works for the demonstration here!