--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
)
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
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
*/