I have an issue with this AFTER INSERT trigger that I'm using for auditing:
Cannot insert the value NULL into column 'TermID', table
'AuditTerms'; column does not allow nulls. INSERT fails.
TermKeys table definition:
TermID int Identity PK
Key int
Action int
AuditTerms table definition:
TermID int PK
UpdatedAt datetime
Trigger definition:
ALTER TRIGGER [dbo].[TRG_TermKeys_AuditTerms_IUD] ON [dbo].[TermKeys]
AFTER INSERT, UPDATE, DELETE NOT FOR REPLICATION AS
BEGIN
SET NOCOUNT ON;
DECLARE @termid int;
IF EXISTS(SELECT * FROM Deleted) SELECT @termid = TermID FROM Deleted;
IF EXISTS(SELECT * FROM Inserted) SELECT @termid = TermID FROM Inserted;
IF EXISTS(SELECT TermID FROM AuditTerms WHERE (TermID = @termid))
BEGIN
UPDATE AuditTerms SET UpdatedAt = getdate() WHERE (TermID = @termid);
END
ELSE
BEGIN
INSERT INTO AuditTerms (TermID, UpdatedAt) VALUES (@termid, getdate());
END;
END;
TermID is an Identity field and the PK, does the Inserted table contain the Identity value at this point?
Should I be using @@Identity or some other way?
Best Answer
The error is caused by attempting to insert
AuditTerms
when there is no corresponding primary key inTermKeys
. After creating the table schema and trigger in tempdb and inserting a few records in TermKeys, I was able to reproduce it by issuing a DELETE twice on the same TermID:gives me:
Something in the application code may be running the query twice; I can't see any other way to get a null value. The best way to avoid the error is to add a precautionary check of @@ROWCOUNT at the beginning of the trigger, since it will return the total number of rows affected by the DML statement that fired it.