특정 테이블 INSERT,DELETE ,UPDATE 시 TRIGGER 이용 실행 시간 및 쿼리 문 특정 테이블에 저장 해보기

 

-- 데이터가조작되는테이블

CREATE TABLE TEST_TABLE

(

SERIAL INT IDENTITY(1,1) ,

NAME NVARCHAR(20) ,

PHONE NVARCHAR(15)

)

-- 테이블의INSERT ,DELETE ,UPDATE 로그를남길테이블생성

CREATE TABLE TESTDB.DBO.OPERATION_LOG_TABLE

(

SERIAL INT IDENTITY(1,1) NOT NULL,

RUN_DATE NVARCHAR(10) NOT NULL DEFAULT CONVERT(NVARCHAR(10),GETDATE(),126) ,

RUN_TIME NVARCHAR(80) NOT NULL DEFAULT CONVERT(NVARCHAR(8),GETDATE(),108),

LOGIN_NAME NVARCHAR(200) NULL,

USER_NAME NVARCHAR(200) NULL,

DATABASE_NAME NVARCHAR(200) NULL,

EVENT_TYPE NVARCHAR(100) NULL,

PARAMETERS NVARCHAR(100) NULL,

TSQL_COMMAND NTEXT

)

-- 쿼리실행일자와시간을PRIMARY KEY로설정해준다.

ALTER TABLE TESTDB.DBO.OPERATION_LOG_TABLE ADD CONSTRAINT IX_OPERATION_LOG_TABLE PRIMARY KEY CLUSTERED (RUN_DATE , RUN_TIME ASC)

CREATE INDEX IX_OPERATION_LOG_TABLE_2 ON TESTDB.DBO.OPERATION_LOG_TABLE (SERIAL)

-- 트리거생성부분

CREATE TRIGGER TRG_TEST_TABLE

ON TEST_TABLE

FOR INSERT,UPDATE,DELETE -- INSERT,UPDATE,DELETE 작업시발생

AS

BEGIN

SET NOCOUNT ON

CREATE TABLE #INPUTBUFFER

(

EVENTTYPE NVARCHAR(100),

PARAMETERS VARCHAR(20) ,

TSQLCommand NTEXT

)

-- DBCC INPUTBUFFER를이용해현재SPID의실행쿼리를얻어온다.

INSERT #INPUTBUFFER

EXEC ('DBCC INPUTBUFFER( ' + @@SPID + ')')

INSERT TESTDB.DBO.OPERATION_LOG_TABLE

(

LOGIN_NAME,[USER_NAME],

DATABASE_NAME ,

EVENT_TYPE ,

[PARAMETERS],

TSQL_COMMAND

)

SELECT

LOGINAME ,

NT_USERNAME ,

(SELECT DB_NAME(DBID)),

(SELECT EVENTTYPE FROM #INPUTBUFFER) ,

(SELECT [PARAMETERS] FROM #INPUTBUFFER) ,

(SELECT LTRIM(RTRIM(CONVERT(NVARCHAR(MAX),TSQLCommand))) FROM #INPUTBUFFER)

FROM SYS.SYSPROCESSES WHERE SPID = @@SPID

END

-- 만약TEST_TABLE에INSERT하는USER 가SYS.SYSPROCESSES 의권한이없다면실행이막힐거같습니다.

DROP TRIGGER TRG_TEST_TABLE --트리거삭제