sql - drop trigger if exists and create -

i check if trigger exists on [tbl] , create one. tried way didn't work. doing wrong?

if exists (select * sys.objects [name] = '[dbo].[trg]' , [type] = 'tr')       drop trigger [dbo].[trg] on [dbo].[tbl] go create trigger [dbo].[trg] on [dbo].[tbl]  after delete begin    // end go 

the [name] field in sys.objects contain actual name (i.e. trg), not including schema (i.e. dbo in case) or text qualifiers (i.e. [ , ] in case).

and, don't specify table name drop trigger since trigger object (unlike indexes). need remove on clause (which used ddl , logon triggers).

if exists (select * sys.objects [name] = n'trg' , [type] = 'tr') begin       drop trigger [dbo].[trg]; end; 

please note should prefix object name string literal n since [name] field sysname datatype equates nvarchar(128).

if did want incorporate schema name, use object_id() function allow schema names , text qualifiers (you need match against object_id instead of name):

if exists (select * sys.objects [object_id] = object_id(n'[dbo].[trg]')                , [type] = 'tr') begin       drop trigger [dbo].[trg]; end; 

and simplify, since object name needs unique within schema, need test existence. if reason different object type exists name, drop trigger fail since other object is, well, not trigger ;-). hence, use following:

if (object_id(n'[dbo].[trg]') not null) begin       drop trigger [dbo].[trg]; end;