MSSQL 프로시저 변경이력 로그 만들기

많은 사이트에서 SP등의 로그는 형상관리하지 않는것을 경험했다.

 

아래는 SP의 ALTER CREATE DTOP 로그를 특정 테이블에 넣는 부분을 추가한것이다.

 

 

1) 변경로그 테이블 만들기

 

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[PROC_ALTER_LOG](
	[LogID] [int] IDENTITY(1,1) NOT NULL,
	[EventType] [nvarchar](max) NULL,
	[ObjectName] [nvarchar](max) NULL,
	[ObjectDefinition] [nvarchar](max) NULL,
	[ChangedBy] [nvarchar](max) NULL,
	[ChangedDate] [datetime] NULL,
PRIMARY KEY CLUSTERED 
(
	[LogID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

 

 

2)  트리거 만들기

CREATE TRIGGER TRG_PROC_LOG
ON DATABASE
FOR ALTER_PROCEDURE, CREATE_PROCEDURE, DROP_PROCEDURE
AS
BEGIN
    BEGIN TRY
        DECLARE @data XML;
        SET @data = EVENTDATA();

        DECLARE @eventType NVARCHAR(100);
        DECLARE @objectName NVARCHAR(100);
        DECLARE @objectDefinition NVARCHAR(MAX);
        DECLARE @changedBy NVARCHAR(100);

        SET @eventType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(100)');
        SET @objectName = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(100)');
        SET @objectDefinition = @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)');
        SET @changedBy = @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'NVARCHAR(100)');

        
        INSERT INTO dbo.PROC_ALTER_LOG (
            EventType,
            ObjectName,
            ObjectDefinition,
            ChangedBy,
            ChangedDate
        )
        VALUES (
            @eventType,
            @objectName,
            @objectDefinition,
            @changedBy,
            GETDATE()
        );
    END TRY
    BEGIN CATCH
        -- 오류 처리 코드 작성
        PRINT ERROR_MESSAGE();
    END CATCH;
END;

sample1.sql

0.00MB