Sqlserver 触发器记录删除与修改

86 阅读 0 评论 57 点赞

我是靠谱客的博主无辜水池，这篇文章主要介绍Sqlserver 触发器记录删除与修改，现在分享给大家，希望可以做个参考。

最近项目里发生一些问题发现数据无故被删除只能作一个触发器来记录SQL

需要一个触发器一个log表

log表

复制代码

1
2
3
4
5
6
7
8
CREATE TABLE [dbo].[LOGR](
	[USERID] [varchar](50) NULL,
	[STRSQL] [varchar](max) NULL,
	[DATE] [varchar](8) NULL,
	[TIME] [varchar](8) NULL,
	[TABLNM] [nvarchar](50) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

触发器

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
CREATE TRIGGER [dbo].[KNMT_LOG] ON [dbo].[PATIENT] 
FOR UPDATE, DELETE 
AS

DECLARE @CLIENT AS VARCHAR(50)
DECLARE @DATE AS VARCHAR(8)
DECLARE @TIME AS VARCHAR(8)
DECLARE @USERNAME AS VARCHAR(50)
DECLARE @STATMT  AS VARCHAR(max)
DECLARE @strSQL AS VARCHAR(100)

SET @CLIENT = HOST_NAME()
SET @DATE = CONVERT(VARCHAR(8),GETDATE(),112)
SET @TIME =REPLACE(CONVERT(VARCHAR(8),GETDATE(),108),':','')
SET @strSQL='DBCC INPUTBUFFER('+CAST(@@SPID AS VARCHAR(50))+')'

CREATE TABLE #STATEMENT (C1 VARCHAR(50),C2 VARCHAR(50),C3 VARCHAR(5000))
INSERT INTO #STATEMENT  EXEC(@strSQL)

SELECT @STATMT=C3 FROM #STATEMENT

INSERT INTO LOGR(USERID,STRSQL,DATE,TIME,TABLNM) VALUES(@CLIENT,@STATMT,@DATE,@TIME,'PATIENT')