SQL: Why should CREATE TRIGGER be preceded by GO

Question

When creating an SQL script to create a trigger in a table, I would like to verify that the trigger does not exist before it is created. Otherwise, the script cannot be executed multiple times.

So, I added an instruction to first check if a trigger exists. After adding this statement, the CREATE TRIGGER statement no longer works.

IF NOT EXISTS (SELECT name FROM sysobjects WHERE name = 'tr_MyTable1_INSERT' AND type = 'TR') BEGIN CREATE TRIGGER tr_MyTable1_INSERT ON MyTable1 AFTER INSERT AS BEGIN ... END END GO 

This gives:

 Msg 156, Level 15, State 1, Line 5 Incorrect syntax near the keyword 'TRIGGER'. 

---

The solution is to delete the existing trigger and then create a new one:

 IF EXISTS (SELECT name FROM sysobjects WHERE name = 'tr_MyTable1_INSERT' AND type = 'TR') DROP TRIGGER tr_MyTable1_INSERT GO CREATE TRIGGER tr_MyTable1_INSERT ON MyTable1 AFTER INSERT AS BEGIN ... END GO 

---

My question is : why does the first example not work? What is wrong with trigger checking?

Answer 1

Some statements must be the first in the package (as in a group of expressions separated by GO).

Quote:

CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE RULE, CREATE SCHEMA, CREATE TRIGGER and CREATE VIEW cannot be combined with other statements in the package. The CREATE statement must run the package. All other statements that follow in this batch will be interpreted as part of the definition of the first CREATE statement.

Answer 2

This is just one of the rules for SQL Server packages (see):

http://msdn.microsoft.com/en-us/library/ms175502.aspx

Otherwise, you can change the object, say, a table, and then refer to the change in the same batch before the change has been made.

Answer 3

Schema changes should always be separate batch calls ... I assume they do this to ensure that your SELECT succeeds if you change the schema in the same batch, they may not be able to provide this. Just guess ...