piątek, 23 listopada 2012

Does trigger has a schema? (Microsoft SQL Server)


I was wondering if triggers have (or belongs to) schemas.

According to MSDN syntax for CREATE TRIGGER is:

CREATE TRIGGER [ schema_name . ]trigger_name 
ON { table | view } 
[ WITH <dml_trigger_option> [ ,...n ] ]
{ FOR | AFTER | INSTEAD OF } 
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } 
[ NOT FOR REPLICATION ] 
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME <method specifier [ ; ] > }

But when you expand triggers node under table node in SQL Server Management Studio (SSMS) trigger schema is not shown. Also if you query sys.triggers view there is no schema_id column. But when you query for your trigger name sys.objects views there IS schema_id column. So where is the truth?

So in my lab I made a simple test. I tried to add to one table two triggers with the same name but different schemas, and it occures that:

Msg 2103, Level 15, State 1, Procedure MyNewTrigger, Line 14 Cannot create trigger 'schema2.MyNewTrigger' because its schema is different from the schema of the target table or view.

Conclusion: Trigger has a schema but it has to be the same schema as schema of the table. So it's some way safe to avoid giving schema names together with trigger name in scripts.

Brak komentarzy:

Prześlij komentarz