Friday, April 14, 2006

Does the DDL Trigger exist?

Here's a simple little function that checks whether a DDL Trigger with the specified name already exists. If the trigger exists 1 is returned, else the function returns 0. For most SQL objects I normally use the object_id() system function, but the latter only returns a valid value for objects in the sys.objects system catalog view, while triggers (both DML and DDL triggers), XML Schema collections and a few other objects are not listed there.
create function dbo.fnExists_DDLTrigger
 (
 @name sysname
 )
returns bit
as
begin
 declare @exists bit

 if (exists (
   select *
    from sys.triggers
    where (sys.triggers.parent_class = 0)
     and (sys.triggers.[name] = @name)
   ))
  begin
   set @exists = 1
  end
 else
  begin
   set @exists = 0
  end

 return @exists
end
go
ML p.s.: You might also find this function usefull.

No comments: