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