[MSSQL][트리거] 사용/사용안함

[트리거] 사용/사용안함

 

1. 형식 

 

DISABLE/ENABLE TRIGGER { [ schema_name . ] trigger_name [ ,...n ] | ALL } ON { object_name | DATABASE | ALL SERVER } [ ; ]

 

<예>

-- tblName 테이블 안의 모든 Trigger Disable DISABLE TRIGGER ALL ON tblName GO; -- tblName 테이블 안의 트리거명 "triggerName" Disable DISABLE TRIGGER triggerName ON tblName GO; -- dbName DB 안의 모든 Trigger Disable DISABLE TRIGGER ALL ON dbName GO; -- tblName 테이블 안의 모든 Trigger Enable ENABLE TRIGGER ALL ON tblName GO; -- tblName 테이블 안의 트리거명 "triggerName" Enable ENABLE TRIGGER triggerName ON tblName GO; -- dbName DB 안의 모든 Trigger Disable ENABLE TRIGGER ALL ON dbName GO;

 

 

2. ALTER TABLE 사용

 

ALTER TABLE object_name DISABLE TRIGGER { [ schema_name . ] trigger_name [ ,...n ] | ALL }

 

<예>

-- tblName 테이블 안의 모든 Trigger Disable ALTER TABLE tblName DISABLE TRIGGER ALL GO; -- tblName 테이블 안의 트리거명 "triggerName" Disable ALTER TABLE tblName DISABLE TRIGGER triggerName GO; >-- tblName 테이블 안의 모든 Trigger Enable ALTER TABLE tblName ENABLE TRIGGER ALL GO; -- tblName 테이블 안의 트리거명 "triggerName" Enable ALTER TABLE tblName ENABLE TRIGGER triggerName GO;

 

 

3. 트리거 리스트 조회

 

<예 : 트리거명,소유자,스키마,테이블,IsUpdate,IsDelete,IsInsert,IsAfter,IsInsteadof,disabled >

SELECT A.[name] AS [trigger_name], USER_NAME(A.[uid]) AS [trigger_owner], USER_NAME(B.[uid]) AS [table_schema], OBJECT_NAME(A.[parent_obj]) AS [table_name], OBJECTPROPERTY( A.[id], 'ExecIsUpdateTrigger') AS [isupdate], OBJECTPROPERTY( A.[id], 'ExecIsDeleteTrigger') AS [isdelete], OBJECTPROPERTY( A.[id], 'ExecIsInsertTrigger') AS [isinsert], OBJECTPROPERTY( A.[id], 'ExecIsAfterTrigger') AS [isafter], OBJECTPROPERTY( A.[id], 'ExecIsInsteadOfTrigger') AS [isinsteadof], OBJECTPROPERTY(A.[id], 'ExecIsTriggerDisabled') AS [disabled] FROM sysobjects AS A INNER JOIN sysobjects AS B ON B.Id = A.parent_obj WHERE A.[type] = 'TR'

 

 

<예 : Disable 트리거만 가져오기 > 

SELECT * FROM sys.triggers WHERE is_disabled = 1