프로시저 변경 이력 남기기

CREATE TABLE dbo.splog
  (
     일련번호    INT IDENTITY(1, 1) NOT NULL,
     오브젝트명 VARCHAR(100) NULL,
     구분          VARCHAR(20) NULL,
     sqlcmd       VARCHAR(max) NULL,
     수정자       VARCHAR(20) NULL,
     수정자ip     VARCHAR(max) 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()

    DECLARE @IP VARCHAR(max)

    SET @IP = (SELECT TOP 1 C.client_net_address
               FROM   sys.sysprocesses S,
                      sys.dm_exec_connections C
               WHERE  S.spid = C.session_id
                      AND S.spid IN (SELECT @@SPID))

    INSERT INTO dbo.splog
                (오브젝트명,
                 구분,
                 sqlcmd,
                 수정자,
                 수정자ip,
                 수정일)
    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(),
                 @IP,
                 Getdate()) 

출처 : http://www.sqler.com/491522

아이피도 남기도록 하나 추가.