SQL Server 2005 Delete Trigger select from deleted returns null
I have an hl7_lock table in SQL Server with these columns:
hl7_id lock_dtm lock_user
I have three triggers on a table for UPDATE, INSERT and DELETE
The table is never updated, but the INSERT trigger works fine. However, this delete trigger sometimes puts an entry in the 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())
Other fields were inserted successfully, but hl7_id is null. Deleted records do not have a null hl7_id.
We installed SQL Server SP 2 over the weekend, but that didn't make things better. Has anyone seen this behavior in a SQL Server trigger. Any advice on how to fix my trigger?
a source to share
Does this happen when only one record is deleted or when multiple records are deleted? the trigger will fail if more than one entry is deleted.
you should do something more similar:
insert into hl7_lock_log (username, action, hl7_id, dtm) select system_user, Delete, hl7_id, getdate () from remote
a source to share
Are you sure the records are in the "deleted" table? If a delete statement is running on the main table that deletes null records, it looks like your code above will still be inserted into your lock log.
Maybe check if there are any entries in the delete before inserting fixes the problem.
As Thirster also points out, you need to set up multiple records to delete in a single statement.
a source to share