在记录删除处的删除触发器中检索删除

Question

我有一个表。我正在使用触发器来记录记录的更改。有一个字段名称"更新"，该名称已记录了执行操作的人（例如插入，更新，删除）

可以插入和更新，因为我将与其他参数一起插入或更新字段，例如

INSERT INTO T (P1,UPDATEDBY) SELECT 'HELLO','ME'
UPDATE T SET P1 ='WORLD', UPDATEDBY = 'ME'

对于这些操作，我可以发现"我"插入/更新了记录，以便我可以正确创建日志。但是，如何告诉数据库谁删除了记录？由于我只是执行

DELETE FROM T WHERE P1='WORLD'

谢谢。

Answer 1

然后，您还需要一个表格，将活动日志保存为crud（创建，更新，删除）

与至少3个参数

• 名称
• 日期
• 动作

然后，只要您进行crud操作或至少删除案例

Answer 2

如果要跟踪记录的更改，请更好地使用SQL Server中可用的魔术表（插入，已删除）。这两个表将有有关插入，删除和更新记录的详细信息。

请按照链接进行参考，

http://www.c-sharpcorner.com/uploadfile/93126e/what--are-are-the-magic-tables-used-by-triggers-inger-triggers-in-sql-server/

SQL Server 2000中有哪些魔术表？

，或者如果您想带上当前用户，则

使用SQL触发器

Answer 3

如果您是通过ado.net或实体框架中的应用程序删除的，则无法跟踪谁删除记录，您可以像我一样使用一个简单的技巧最小的更改非常适合工作，并且没有额外的列。

ALTER trigger [dbo].[CreateLogCart]
on [dbo].[Cart]
after UPDATE, INSERT, DELETE
as
declare @targetID bigint,
@user int, 
@activity varchar(10);
if exists(SELECT 1 from inserted) and exists (SELECT 1 from deleted)
begin
    SET @activity = 'UPDATE';
    **-- update createdby before delete and do not log**-----------------------------
    declare @iby int;
    declare @dby int;
    **-- update createdvy before delete and do not log**------------------------------
    SELECT @targetID = d.CartId, @dby = d.CreatedBy from deleted d;
    SELECT @user = d.modifiedby, @iby = d.CreatedBy from inserted d;
    if(@iby = @dby) **-- update createdby before delete and do not log** --------
    begin
      INSERT into AuditCart(CartId, UserId, AppId, Description, CreatedBy, CreatedOn, ModifiedBy, ModifiedOn, Activity, ActivityBy, ActivityOn)
    select @targetID, d.userid, d.AppId, d.Description, d.CreatedBy, d.CreatedOn, d.ModifiedBy, d.ModifiedOn, @activity, @user, getdate() from deleted d
    end
end
If exists (Select 1 from inserted) and not exists(Select 1 from deleted)
begin
    SET @activity = 'INSERT';
    SELECT @targetID = i.CartId from inserted i;
    SELECT @user = d.modifiedby from inserted d;
     INSERT into AuditCart(CartId, UserId, AppId, Description, CreatedBy, CreatedOn, ModifiedBy, ModifiedOn, Activity, ActivityBy, ActivityOn)
     select @targetID, d.userid, d.AppId, d.Description, d.CreatedBy, d.CreatedOn, d.ModifiedBy, d.ModifiedOn, @activity, @user, getdate() from inserted d
end
If exists(select 1 from deleted) and not exists(Select 1 from inserted)
begin 
    **-- revert createdby updated in delete operation**--------------
    declare @cby int;
     SET @cby = (select top 1 CreatedBy from dbo.AuditCart where Activity = 'INSERT' and CartId = (select d.CartId from deleted d) order by CreatedOn desc);  
    **-- revert createdby updated in delete operation**-------------
    SET @activity = 'DELETE';
    SELECT @targetID = i.CartId from deleted i;
    SELECT @user = d.CreatedBy from deleted d;
    INSERT into AuditCart(CartId, UserId, AppId, Description, CreatedBy, CreatedOn, ModifiedBy, ModifiedOn, Activity, ActivityBy, ActivityOn)
    select @targetID, d.userid, d.AppId, d.Description, ****@cby****, d.CreatedOn, d.ModifiedBy, d.ModifiedOn, @activity, @user, getdate() from deleted d
end

您只需要在删除之前更新创建，并且如果在更新触发器中的条件，请勿在更新触发器中记录它现在，在删除触发器中，您只需要通过从旧日志中获得创建的元素来将其恢复，因为在我的情况下，其价值将其删除为 activeby