프로시저를 수정하다보면 간혹 실수를 하거나 이전 구문으로 복원을 해야할 때 가있다.

데이터베이스에 트리거를 등록해두고

프로시저에 CREATE나 ALTER등이 될 때 마다 소스를 지정한 테이블에 백업하는 방법이다.

CREATE TABLE dbo.SPLOG(
 idx bigint IDENTITY(1,1) NOT NULL,
 sp_name varchar(100) NULL,
 division varchar(20) NULL,
 SQLCMD varchar(max) NULL,
 author varchar(20) NULL,
 modified_date datetime NULL,
 CONSTRAINT XPKSPLOG PRIMARY KEY NONCLUSTERED
(
 idx ASC
))

GO


CREATE TRIGGER TRG_SPLOG ON DATABASE
FOR
CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,
CREATE_VIEW, ALTER_VIEW, DROP_VIEW,
CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION,
CREATE_TRIGGER, ALTER_TRIGGER, DROP_TRIGGER

AS

DECLARE @DATA XML

SET @DATA = EVENTDATA()

INSERT INTO DBO.SPLOG (sp_name, division, SQLCMD, author, modified_date)
VALUES
(@DATA.value('(/EVENT_INSTANCE/ObjectName)[1]', 'VARCHAR(100)'),
 @DATA.value('(/EVENT_INSTANCE/EventType)[1]', 'VARCHAR(100)'),
 @DATA.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'VARCHAR(MAX)'),
 HOST_NAME(),
 GETDATE())

이제 수정이 이루어질때마다 SPLOG 테이블에 이력이 남게될 것이다.

• 추가

프로시저의 문장 길이가 varchar(max)가 허용하는 길이를 넘어서는 경우가 있다.

그럴 경우엔 한도 길이만큼 잘라서 분할해서 저장하면 온전히 보관할 수 있다.

프로시저 로그 저장용 테이블 생성

USE [DB]
GO

/****** Object:  Table [dbo].[SPLOG]    Script Date: 2021-06-03 오전 11:04:17 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[SPLOG](
  [idx] [bigint] IDENTITY(1,1) NOT NULL,
  [sp_name] [varchar](100) NULL,
  [division] [varchar](20) NULL,
  [SQLCMD] [nvarchar](max) NULL,
  [author] [varchar](20) NULL,
  [modified_date] [datetime] NULL,
  [page] [int] NULL,
  [max_page] [int] NULL,
 CONSTRAINT [XPKSPLOG] PRIMARY KEY NONCLUSTERED 
(
  [idx] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

트리거 생성

USE [DB]
GO

/****** Object:  DdlTrigger [TRG_SPLOG]    Script Date: 2021-06-03 오전 11:00:37 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE TRIGGER [TRG_SPLOG] ON DATABASE
FOR
CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,
CREATE_VIEW, ALTER_VIEW, DROP_VIEW,
CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION,
CREATE_TRIGGER, ALTER_TRIGGER, DROP_TRIGGER

AS

DECLARE @DATA XML
DECLARE @NUM INT, @MAX INT,@INTERVAL INT, @INDEX INT, @TMPTEXT VARCHAR(MAX), @MERGETEXT VARCHAR(MAX), @SP_NAME VARCHAR(100), @LOG_COUNT INT

SET @DATA = EVENTDATA()

SET @SP_NAME = @DATA.value('(/EVENT_INSTANCE/ObjectName)[1]', 'VARCHAR(100)')
SET @NUM = 1
--해당 프로시저의 최대 페이지를 가져온다
SET @MAX = (SELECT max(colid)
FROM syscomments a,
  sysobjects b
where B.xtype  in ('P','FN')
AND A.ID = B.ID
and b.name = @SP_NAME)

SET @INTERVAL = 1
SET @INDEX = 1
SET @TMPTEXT = ''
SET @MERGETEXT = ''

IF @MAX > 9 --페이지수가 9페이지를 넘는다면 분할해서 저장하자
  BEGIN
    WHILE @NUM<=@MAX
      BEGIN   
          SET @TMPTEXT = (SELECT A.TEXT
          FROM syscomments a,
            sysobjects b
          where B.xtype  in ('P','FN')
          AND A.ID = B.ID
          and b.name = @SP_NAME
          and colid = @NUM)

          IF @NUM = 1
            BEGIN
              SET @TMPTEXT = REPLACE(@TMPTEXT, 'CREATE', 'ALTER')
            END

          SET @MERGETEXT = @MERGETEXT + @TMPTEXT

          SET @NUM = @NUM + 1
          SET @INTERVAL = @INTERVAL + 1

          IF @INTERVAL = 9 OR @NUM>@MAX
          BEGIN
            --SELECT @MERGETEXT, @INDEX
            INSERT INTO DBO.SPLOG (sp_name, division, SQLCMD, author, modified_date, page, max_page)
            VALUES
            (@DATA.value('(/EVENT_INSTANCE/ObjectName)[1]', 'VARCHAR(100)'),
             @DATA.value('(/EVENT_INSTANCE/EventType)[1]', 'VARCHAR(100)'),
             @MERGETEXT,
             HOST_NAME(),
             GETDATE(),
             @INDEX,
             @MAX/9 + 1)
            SET @INTERVAL = 1
            SET @INDEX = @INDEX + 1
            SET @MERGETEXT = ''
          END
      END;
  END
ELSE
  BEGIN
    INSERT INTO DBO.SPLOG (sp_name, division, SQLCMD, author, modified_date, page, max_page)
    VALUES
    (@DATA.value('(/EVENT_INSTANCE/ObjectName)[1]', 'VARCHAR(100)'),
     @DATA.value('(/EVENT_INSTANCE/EventType)[1]', 'VARCHAR(100)'),
     @DATA.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'VARCHAR(MAX)'),
     HOST_NAME(),
     GETDATE(),
     @INDEX,
     @INDEX)
  END

--저장된로그가 200개가 넘으면 이전걸 지워주자
SET @LOG_COUNT = (select count(*) from SPLOG where sp_name = @SP_NAME)

IF @LOG_COUNT > 200 
  BEGIN
    delete from SPLOG
    where sp_name = @SP_NAME
    and idx not in (select top 200 idx from SPLOG
    where sp_name = @SP_NAME
    order by modified_date desc)
  END


GO

SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO

ENABLE TRIGGER [TRG_SPLOG] ON DATABASE
GO

MS-SQL 프로시저 백업 트리거

김키티