每日删除行

发布于 2024-12-24 16:29:15 字数 239 浏览 3 评论 0原文

我有一个数据库表，在每天的特定时间，在特定条件下的相同数据会丢失，就像执行这样的语句一样：

delete * from table where category=1

我想通过 SQL 脚本列出该表上的所有删除操作，以了解如何准确删除记录以及删除的语句、用户和时间。

有人有这样的脚本吗？或者有谁有类似的情况可以指教吗？

SQL版本为Server 2008企业版。

原文

I have a database table from which same data under a certain condition are lost at a specific time daily as if such statement is performed:

delete * from table where category=1

I'd like to list all delete actions on this table through a SQL script to know how records are deleted exactly and by which statement, user and time of deletion.

Does anyone have such script? Or did anyone have similar case and can advise?

The SQL version is Server 2008 Enterprise Edition.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

ペ泪落弦音 2024-12-31 16:29:15

如果这只是一个短期调试问题，解决此问题的最简单方法可能是运行 SQL Server Profiler，并设置过滤器来捕获您感兴趣的数据。这样就无需更改任何代码。

为了获得最佳性能，如果可以的话，请尝试在数据库服务器以外的计算机上运行 SQL Profiler。

回复收藏 0 原文

云巢 2024-12-31 16:29:15

在表上使用 AFTER DELETE 触发器可在另一个表中记录删除操作以及执行操作的用户和时间。

使用一些高级技巧，您可以提取删除行的查询文本，但我不确定这是否可以在触发器内实现。

触发器可能看起来像这样

CREATE TABLE YourLogTable
(
  ID int identity primary key,
  Date datetime NOT NULL DEFAULT GETDATE(),
  [User] nvarchar(128) NOT NULL DEFAULT suser_sname(),
  [SqlText] NVARCHAR(MAX),
  [any other interesting columns from deleted rows]
)
GO

CREATE TRIGGER [TR.AD@YourTable]
ON YourTable
AFTER DELETE
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @sqlText NVARCHAR(MAX)

  SELECT @sqlText = txt.Text
  FROM sys.dm_exec_connections c
  CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) txt
  WHERE session_id = @@SPID

  INSERT YourLogTable([SqlText], [any other interesting columns from deleted rows])
  SELECT @SqlText, [any other interesting columns from deleted rows]
  FROM DELETED

END

Use AFTER DELETE trigger on the table to log deletions in another table with user and time it was performed.

Using some advanced tricks you can extract the query text which deleted the rows, but I'm not sure that it is possible inside a trigger.

The trigger might look like this

CREATE TABLE YourLogTable
(
  ID int identity primary key,
  Date datetime NOT NULL DEFAULT GETDATE(),
  [User] nvarchar(128) NOT NULL DEFAULT suser_sname(),
  [SqlText] NVARCHAR(MAX),
  [any other interesting columns from deleted rows]
)
GO

CREATE TRIGGER [TR.AD@YourTable]
ON YourTable
AFTER DELETE
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @sqlText NVARCHAR(MAX)

  SELECT @sqlText = txt.Text
  FROM sys.dm_exec_connections c
  CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) txt
  WHERE session_id = @@SPID

  INSERT YourLogTable([SqlText], [any other interesting columns from deleted rows])
  SELECT @SqlText, [any other interesting columns from deleted rows]
  FROM DELETED

END

回复收藏 0 原文

~没有更多了~