🗃

MSSQL DDL을 이용한 소스코드관리

CREATE TABLE dbo.SPLOG(
    일련번호 int IDENTITY(1,1) NOT NULL,
    오브젝트명 varchar(100) NULL,
    구분 varchar(20) NULL,
    SQLCMD varchar(max) NULL,
    수정자 varchar(20) NULL,
    수정일 datetime NULL,
    CONSTRAINT XPKSPLOG PRIMARY KEY NONCLUSTERED
        (
         일련번호 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 (오브젝트명, 구분, SQLCMD, 수정자, 수정일)
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())
SQL
복사

CREATE TABLE TABLELOG
(
             DDL_Event_Time            datetime
             ,
             DDL_Login_Name            varchar(150)
             ,
             DDL_User_Name             varchar(150)
             ,
             DDL_Database_Name         varchar(150)
             ,
             DDL_Schema_Name           varchar(150)
             ,
             DDL_Object_Name           varchar(150)
             ,
             DDL_Object_Type           varchar(150)
             ,
             DDL_Command              varchar(max)
);
 
CREATE TRIGGER Audit_DDL
ON DATABASE
    FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS
     DECLARE
        @event xml;
     SET
     @event = EVENTDATA();
     INSERT INTO TABLELOG
     VALUES
     (
     REPLACE(CONVERT(varchar(50),
     @event.query('data(/EVENT_INSTANCE/PostTime)')), 'T', ' ')
     ,
     CONVERT(varchar(150),
     @event.query('data(/EVENT_INSTANCE/LoginName)'))
     ,
     CONVERT(varchar(150),
     @event.query('data(/EVENT_INSTANCE/UserName)'))
     ,
     CONVERT(varchar(150),
     @event.query('data(/EVENT_INSTANCE/DatabaseName)'))
     ,
     CONVERT(varchar(150),
     @event.query('data(/EVENT_INSTANCE/SchemaName)'))
     ,
     CONVERT(varchar(150),
     @event.query('data(/EVENT_INSTANCE/ObjectName)'))
     ,
     CONVERT(varchar(150),
     @event.query('data(/EVENT_INSTANCE/ObjectType)'))
     ,
     CONVERT(varchar(max),
     @event.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)'))
     );
SQL
복사