DataBaser.Net: DDL Trigger

1 예제1 #

--http://databaser.net
--참고: http://www.sqlservercentral.com/articles/SQLServerCentral.com/sqlserver2005logontriggers/2366/
USE AdeventureWorks
GO
 
--서비스 브로커 활성화
ALTER DATABASE AdeventureWorks SET ENABLE_BROKER
GO
 
--DDL트리거에 의해 데이터가 담길 테이블
CREATE TABLE dbo.T_DDLLog
(
	LoggingID int IDENTITY(1,1)
,	EventTime DATETIME
,	EventType NVARCHAR(255)
,	LoginName NVARCHAR(255)
,	HostName NVARCHAR(255)
,	NTUserName NVARCHAR(255)
,	NTDomainName NVARCHAR(255)
,	Success INT
,	FullLog XML
,	Script nvarchar(max)
)
GO

--Try~Catch
CREATE TABLE dbo.T_SP_ErrorLog(
	ErrorNumber int NULL
,	ErrorSeverity int NULL
,	ErrorState int NULL
,	ErrorProcedure nvarchar(126) NULL
,	ErrorLine int NULL
,	ErrorMessage nvarchar(2048) NULL
,	ErrorDatetime datetime NULL
,	Parameters nvarchar(max) NULL
) 
 
CREATE CLUSTERED INDEX CIX_EventTime
ON dbo.T_DDLLog(EventTime DESC);
 
CREATE INDEX NIX_LoggingID
ON dbo.T_DDLLog(LoggingID);
GO
 
--서비스를 위한 설정들(큐, 서비스)
--DROP QUEUE LoggingQueue
CREATE QUEUE LoggingQueue
GO
 
--DROP SERVICE LoggingService
CREATE SERVICE LoggingService
ON QUEUE LoggingQueue
([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification])
GO
 
--이벤트 노티 생성
--DROP EVENT NOTIFICATION Logging_Event_Notification ON SERVER
--ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.ko/wmi9/html/e2916cd7-a3ed-41e6-97b4-2ee060754cbe.htm
CREATE EVENT NOTIFICATION Logging_Event_Notification
ON SERVER
FOR DDL_EVENTS
--,	TRC_CLR
--,	TRC_DEPRECATION
--,	TRC_ERRORS_AND_WARNINGS
--,	TRC_FULL_TEXT
--,	TRC_LOCKS
--,	TRC_OBJECTS
--,	TRC_OLEDB
--,	TRC_PERFORMANCE
--,	TRC_QUERY_NOTIFICATIONS
--,	TRC_SECURITY_AUDIT
--,	AUDIT_LOGIN
--,	AUDIT_LOGOUT
--,	AUDIT_LOGIN_FAILED
--,	AUDIT_LOGIN_CHANGE_PASSWORD_EVENT
--,	TRC_SERVER
--,	TRC_STORED_PROCEDURES
--,	TRC_TSQL
--,	TRC_USER_CONFIGURABLE
TO SERVICE 'LoggingService', 'current database'
GO
 
--서비스 브로커의 큐에서 활성화될 프로시저
CREATE PROCEDURE Up_DDL_Log_Create
AS
SET XACT_ABORT ON 
SET NOCOUNT ON 
 
DECLARE     @message_body XML,
            @message_type_name NVARCHAR(256),
            @dialog UNIQUEIDENTIFIER ;
 
--Endless loop
WHILE (1 = 1)
BEGIN
BEGIN TRANSACTION ;
 
    -- Receive the next available message
	WAITFOR 
	(
		RECEIVE TOP(1)
			@message_type_name=message_type_name
		,	@message_body=message_body
		,	@dialog = conversation_handle
		FROM LoggingQueue
	), TIMEOUT 2000
 
	--Rollback and exit if no messages were found
	IF (@@ROWCOUNT = 0)
	BEGIN
		ROLLBACK TRANSACTION ;
		BREAK ;
	END;
 
   --End conversation of end dialog message
    IF (@message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
    BEGIN
		PRINT 'End Dialog received for dialog # ' + cast(@dialog AS nvarchar(40)) ;
		END CONVERSATION @dialog ;
    END ELSE IF (@message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/Error')
	BEGIN
		PRINT 'End Dialog received for dialog # ' + cast(@dialog AS nvarchar(40)) ;
		END CONVERSATION @dialog ;
	END;
	ELSE
    BEGIN
		BEGIN TRY			
			INSERT INTO dbo.T_DDLLog 
			(
				EventTime
			,	EventType
			,	LoginName
			,	HostName
			,	NTUserName
			,	NTDomainName
			,	Success
			,	FullLog	
			,   Script
			)
			SELECT
				CAST(CAST(@message_body.query('/EVENT_INSTANCE/PostTime/text()') AS VARCHAR(64)) AS DATETIME)
			,	CAST(@message_body.query('/EVENT_INSTANCE/EventType/text()') AS VARCHAR(100))
			,	CAST(@message_body.query('/EVENT_INSTANCE/LoginName/text()') AS VARCHAR(100))
			,	CAST(@message_body.query('/EVENT_INSTANCE/HostName/text()') AS VARCHAR(100))
			,	CAST(@message_body.query('/EVENT_INSTANCE/NTUserName/text()') AS VARCHAR(100))
			,	CAST(@message_body.query('/EVENT_INSTANCE/NTDomainName/text()') AS VARCHAR(100))
			,	CAST(CAST(@message_body.query('/EVENT_INSTANCE/Success/text()') AS VARCHAR(64)) AS INTEGER)
			,	@message_body
			,   @message_body.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
		END TRY
 
		BEGIN CATCH
			IF		(XACT_STATE())	= -1 ROLLBACK; 
			ELSE IF (XACT_STATE())	=  1 COMMIT;
 
			INSERT dbo.T_SP_ErrorLog
			(
				ErrorNumber
			,	ErrorSeverity
			,	ErrorState
			,	ErrorProcedure
			,	ErrorLine
			,	ErrorMessage
			,	ErrorDatetime
			,	Parameters
			)
			SELECT
				ERROR_NUMBER()
			,	ERROR_SEVERITY()
			,	ERROR_STATE()
			,	ERROR_PROCEDURE()
			,	ERROR_LINE()
			,	ERROR_MESSAGE()
			,	GETDATE()
			,	CAST(@message_body AS nvarchar(max));	-- nvarchar(max) (파라미터를 넣는다.)
 
			RETURN ERROR_NUMBER();
		END CATCH;
	END;
COMMIT TRANSACTION
END
GO
 
--큐 변경
--DROP QUEUE LoggingQueue
ALTER QUEUE LoggingQueue
WITH ACTIVATION (
STATUS = ON,
PROCEDURE_NAME = dbo.Up_DDL_Log_Create ,
MAX_QUEUE_READERS = 2, EXECUTE AS SELF ), STATUS = ON
GO
 
--ALTER QUEUE dbo.LoggingQueue WITH STATUS = ON 
--ALTER QUEUE dbo.LoggingQueue WITH STATUS = OFF
 
/*
--Test
drop table test
CREATE TABLE test(id int)
GO
 
SELECT 
	EventTime
,	FullLog.value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(255)') LoginName
,	FullLog.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(255)') EventType
,	FullLog.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)') CommandText
FROM dbo.T_DDLLog
WHERE LoggingID = 2
*/
 
CREATE PROC dbo.Up_DDL_Log_Read
	@BeginDT datetime
,	@EndDT datetime
,	@LoggingID int = 0
,	@EventType nvarchar(255) = ''
,	@LoginName nvarchar(255) = ''
,	@HostName nvarchar(255) = ''
,	@NTUserName nvarchar(255) = ''
,	@NTDomainName nvarchar(1000) = ''
,	@SQLViewYN bit = 0
AS
SELECT 
	LoggingID
,	EventTime
,	EventType
,	LoginName
,	HostName
,	NTUserName
,	NTDomainName
,	Success
,	FullLog
--,	CASE WHEN @SQLViewYN = 0 THEN '' ELSE FullLog.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)') END SQLSource
FROM dbo.T_DDLLog
WHERE 1=1
AND EventTime BETWEEN @BeginDT AND @EndDT
GO
 
/*
 
SELECT * FROM dbo.T_DDLLog
where EventType like '%LOGIN%'
 
select top 100 * from LoggingQueue
 
 
truncate table T_DDLLog
SELECT top 100 * FROM LoggingQueue
*/
Contents

1 예제1 #

2 예제2 - 데이터베이스 수준의 트리거 #

3 예제3 - 서버 수준의 트리거 #

4 참고자료 #
