뷰, 펑션등을 수정할때마다 쏘스를 저장하는 방법

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())