MSSQL 트리거 보기 (DBA 관리용 시스템 프로시저)

/************************************************************************************************

-- sp_trigger0

-- 해당 DB의 모든 트리거나 특정 오브젝트의 트리거를 출력한다.

************************************************************************************************/

USE MASTER

GO

IF OBJECT_ID('sp_trigger0') IS NOT NULL

   DROP PROCEDURE sp_trigger0

GO

CREATE PROC sp_trigger0 

  @objname VARCHAR(100) = NULL

AS

  SET NOCOUNT ON

  IF (@objname IS NULL)

    SELECT name "TRG_NAME"

         , user_name(uid) "TRG_OWNER"

         , ObjectProperty( id, 'ExecIsUpdateTrigger') "IS_UPDATE"

         , ObjectProperty( id, 'ExecIsDeleteTrigger') "IS_DELETE"

         , ObjectProperty( id, 'ExecIsInsertTrigger') "IS_INSERT"

         , ObjectProperty( id, 'ExecIsAfterTrigger') "AFTER_TRG"

         , ObjectProperty( id, 'ExecIsInsteadOfTrigger') "INSEAD_OF_TRG"

    FROM sysobjects

    WHERE type = 'TR' 

  ELSE

  BEGIN

    IF NOT EXISTS(SELECT ID

                  FROM sysobjects

                  WHERE id = object_id(@objname)

                    AND xtype IN ('S','U', 'V')

                 )

    BEGIN

      RAISERROR('There is no ''%s'' object(xtype:s,u,v) in this database...', 16, 1,@objname)

      RETURN(1)

    END

    SELECT name "TRG_NAME"

         , user_name(uid) "TRG_OWNER"

         , ObjectProperty( id, 'ExecIsUpdateTrigger') "IS_UPDATE"

         , ObjectProperty( id, 'ExecIsDeleteTrigger') "IS_DELETE"

         , ObjectProperty( id, 'ExecIsInsertTrigger') "IS_INSERT"

         , ObjectProperty( id, 'ExecIsAfterTrigger') "AFTER_TRG"

         , ObjectProperty( id, 'ExecIsInsteadOfTrigger') "INSTEADOF_TRG"

    FROM sysobjects

    WHERE parent_obj = object_id(@objname)

      AND type = 'TR'

  END

  RETURN(0) --sp_trigger0

  SET NOCOUNT OFF

GO

  

GRANT EXECUTE ON sp_trigger0 TO PUBLIC

GO