SQL sp_helptrigger2

sp_helptrigger2

USE master
GO

CREATE procedure sp_helptrigger2  --- 
    @tabname nvarchar(776),	        /*      Table name	*/
	@triggertype char(6) = NULL	/*	Trigger type	*/
as

/*
**	Variation of supplied procedure sp_helptrigger
**	    Created By:
**		Kalen Delaney
**		31 July 2000
**
**	Provides ordering information for AFTER TRIGGERS
*/

declare @objid int,        /* id of the object */
		@dbname sysname,
		@deltrig int,
		@instrig int,
		@updtrig int

-- Check to see that the object names are local to the current database.
select @dbname = parsename(@tabname,3)

if @dbname is not null and @dbname <> db_name()
begin
		raiserror(15250,-1,-1)
		return (1)
end

select @objid =  id from sysobjects where id = object_id(@tabname)
	and type in ('S','U', 'V')

if @objid is null
	begin
		select @dbname = db_name()
		raiserror(15009,-1,-1,@tabname,@dbname)
		return(1)
	end

/*	Check that input type is UPDATE, INSERT, DELETE	*/
if @triggertype  is not null and not UPPER(@triggertype ) in ('UPDATE', 'INSERT', 'DELETE')
      begin
         raiserror(15305,-1,-1)
         return(1)
      end

if @triggertype  is NULL
	select
	trigger_name = name,
	trigger_owner = user_name(uid),
	IsUpdate = ObjectProperty( id, 'ExecIsUpdateTrigger'),
        UpdateOrd = CASE ObjectProperty( id, 'ExecIsUpdateTrigger')
                WHEN 0 THEN 'n/a'
		ELSE
		   CASE ObjectProperty( id, 'ExecIsFirstUpdateTrigger')
                	WHEN 1 THEN 'First'
			ELSE 
			    CASE ObjectProperty( id, 'ExecIsLastUpdateTrigger')
				WHEN 1 THEN 'Last'
				ELSE 'Unspecified'
			    END
		    END
		END,	
	IsDelete = ObjectProperty( id, 'ExecIsDeleteTrigger'),
        DeleteOrd = CASE ObjectProperty( id, 'ExecIsDeleteTrigger')
                WHEN 0 THEN 'n/a'
		ELSE
		   CASE ObjectProperty( id, 'ExecIsFirstDeleteTrigger')
                	WHEN 1 THEN 'First'
			ELSE 
			    CASE ObjectProperty( id, 'ExecIsLastDeleteTrigger')
				WHEN 1 THEN 'Last'
				ELSE 'Unspecified'
			    END
		    END
		END,	
	IsInsert = ObjectProperty( id, 'ExecIsInsertTrigger'),
        InsertOrd = CASE ObjectProperty( id, 'ExecIsInsertTrigger')
                WHEN 0 THEN 'n/a'
		ELSE
		   CASE ObjectProperty( id, 'ExecIsFirstInsertTrigger')
                	WHEN 1 THEN 'First'
			ELSE 
			    CASE ObjectProperty( id, 'ExecIsLastInsertTrigger')
				WHEN 1 THEN 'Last'
				ELSE 'Unspecified'
			    END
		    END
		END,	
	IsAfter = ObjectProperty( id, 'ExecIsAfterTrigger'),
	IsInsteadof = ObjectProperty( id, 'ExecIsInsteadOfTrigger')
	from sysobjects
	where parent_obj = @objid and type = 'TR'
else
begin
	set @deltrig = case
		when  upper(@triggertype ) = 'DELETE' then 0
		else -1 end
	set @instrig = case
		when  upper(@triggertype ) = 'INSERT' then 0
		else  -1 end
	set @updtrig = case
		when  upper(@triggertype ) = 'UPDATE' then 0
		else -1 end
	select
	trigger_name = name,
	trigger_owner = user_name(uid),
	IsUpdate = ObjectProperty( id, 'ExecIsUpdateTrigger'),
        UpdateOrd = CASE ObjectProperty( id, 'ExecIsUpdateTrigger')
                WHEN 0 THEN 'n/a'
		ELSE
		   CASE ObjectProperty( id, 'ExecIsFirstUpdateTrigger')
                	WHEN 1 THEN 'First'
			ELSE 
			    CASE ObjectProperty( id, 'ExecIsLastUpdateTrigger')
				WHEN 1 THEN 'Last'
				ELSE 'Unspecified'
			    END
		    END
		END,	
	IsDelete = ObjectProperty( id, 'ExecIsDeleteTrigger'),
        DeleteOrd = CASE ObjectProperty( id, 'ExecIsDeleteTrigger')
                WHEN 0 THEN 'n/a'
		ELSE
		   CASE ObjectProperty( id, 'ExecIsFirstDeleteTrigger')
                	WHEN 1 THEN 'First'
			ELSE 
			    CASE ObjectProperty( id, 'ExecIsLastDeleteTrigger')
				WHEN 1 THEN 'Last'
				ELSE 'Unspecified'
			    END
		    END
		END,	
	IsInsert = ObjectProperty( id, 'ExecIsInsertTrigger'),
        InsertOrd = CASE ObjectProperty( id, 'ExecIsInsertTrigger')
                WHEN 0 THEN 'n/a'
		ELSE
		   CASE ObjectProperty( id, 'ExecIsFirstInsertTrigger')
                	WHEN 1 THEN 'First'
			ELSE 
			    CASE ObjectProperty( id, 'ExecIsLastInsertTrigger')
				WHEN 1 THEN 'Last'
				ELSE 'Unspecified'
			    END
		    END
		END,	
	IsAfter = ObjectProperty( id, 'ExecIsAfterTrigger'),
	IsInsteadof = ObjectProperty( id, 'ExecIsInsteadOfTrigger')
	from sysobjects
	where parent_obj = @objid and
	ObjectProperty( id, 'ExecIsDeleteTrigger') > @deltrig and
	ObjectProperty( id, 'ExecIsInsertTrigger') > @instrig and
	ObjectProperty( id, 'ExecIsUpdateTrigger') > @updtrig and
	type = 'TR'
end
return(0)  --sp_helptrigger