SQL Server 2005 Delete Trigger select from deleted returns null

Question

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

hl7_id lock_dtm lock_user

I have 3 triggers on the table for UPDATE, INSERT, and 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

    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.

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?

Answer 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

Answer 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.