SQL Server 2005删除从已删除的触发器选择返回null

I have a table called hl7_lock in SQL Server with these columns:

我在SQL Server中有一个名为hl7_lock的表,其中包含以下列:

hl7_id lock_dtm lock_user

I have 3 triggers on the table for UPDATE, INSERT, and DELETE

我在表上有3个触发器用于UPDATE,INSERT和DELETE

The table is never updated, but INSERT trigger is working perfectly. However this delete trigger occasionally puts a record in hl7_lock_log with a null hl7_id

该表永远不会更新,但INSERT触发器工作正常。但是,这个删除触发器偶尔会在hl7_lock_log中创建一个带有空hl7_id的记录

    SET NOCOUNT ON;
declare @hl7_id decimal(18,0)
select @hl7_id = hl7_id from deleted
insert into hl7_lock_log (user_name, action, hl7_id, dtm)                        
        values(system_user, 'Delete', @hl7_id,getdate())

The other fields are inserted successfully, but hl7_id is null. The deleted records do not have a null hl7_id.

其他字段已成功插入,但hl7_id为null。删除的记录没有空的hl7_id。

We installed SQL Server SP 2 over the weekend, but that has not improved the situation. Has anyone seen this behavior in a SQL Server trigger. Any advice on how to fix my trigger?

我们在周末安装了SQL Server SP 2,但这并没有改善这种情况。有没有人在SQL Server触发器中看到此行为。关于如何修复触发器的任何建议?

2 个解决方案

#1

does this happen when just one record is deleted, or when multiple records are deleted? the trigger will fail if more than one record is deleted.

如果只删除一条记录,或删除多条记录,是否会发生这种情况?如果删除多个记录,则触发器将失败。

you should do something more like this:

你应该做更像这样的事情:

insert into hl7_lock_log (user_name, action,hl7_id, dtm) select system_user, 'Delete', hl7_id, getdate() from deleted

插入hl7_lock_log(user_name,action,hl7_id,dtm)从删除中选择system_user,'Delete',hl7_id,getdate()

#2

Are you sure that records are in the 'deleted' table? If a delete statement is run on your main table that deletes zero records, it looks like your code above will still insert into your lock log.

您确定记录在“已删除”表中吗?如果在主表上运行删除语句删除零记录,则上面的代码仍然会插入到锁定日志中。

Perhaps checking if there are any records in deleted before inserting would solve the issue.

也许在插入之前检查是否有任何记录被删除可以解决问题。

As Thirster points out as well, you'll need to adjust for multiple records being deleted in a single statement.

正如Thirster所指出的那样,您需要在单个语句中调整要删除的多个记录。

#1