Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
4  
You might get that error if the insert statement inserted zero rows. Your trigger is broken for inserts of multiple rows too. Try INSERT INTO AuditTerms (TermID, UpdatedAt) SELECT TermId, GetDate() FROM Inserted. If that doesn't help post more code. –  Martin Smith Jun 4 at 10:26
    
@MartinSmith - I've removed a lot of code to try and illustrate the issue, there's a similar statement for checking the Deleted table, it's an INSERT, UPDATE, DELETE trigger. Should I separate the logic into discrete INSERT, UPDATE and DELETE triggers? –  SteB Jun 4 at 10:53
    
Please post the table definition and complete trigger definition. –  Martin Smith Jun 4 at 12:31
    
@MartinSmith - I've updated the question as requested –  SteB Jun 4 at 13:00
    
@ypercube - I've added the TermKeys def and corrected the AuditTerms definition. –  SteB Jun 4 at 13:15

1 Answer 1

up vote 1 down vote accepted

The error is caused by attempting to insert AuditTerms when there is no corresponding primary key in TermKeys. 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:

DELETE FROM TermKeys
WHERE TermID = 1

DELETE FROM TermKeys
WHERE TermID = 1

gives me:

Msg 515, Level 16, State 2, Procedure TRG_TermKeys_AuditTerms_IUD, Line 15 Cannot insert the value NULL into column 'TermID', table 'tempdb.dbo.AuditTerms'; column does not allow nulls. INSERT fails. The statement has been terminated.

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.

ALTER TRIGGER [dbo].[TRG_TermKeys_AuditTerms_IUD] ON [dbo].[TermKeys] 
AFTER INSERT, UPDATE, DELETE NOT FOR REPLICATION AS
BEGIN
  -- If the trigger was fired but no rows were affected, do nothing 
  IF @@ROWCOUNT = 0
    RETURN;
  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;
share|improve this answer
    
Removing the AuditTerms table isn't an option since we need the datetime of any deletes. I've re-read the MSDN article and didn't realise an update was essentially a Delete followed by an Insert and would therefore have data in the Deleted table. I'm having trouble following your logic how this causes the issue though. –  SteB Jun 5 at 8:12
    
Sorry, I think I read more into the problem than was there. I'll update the answer accordingly. What statements on the TermKeys table cause the error, is it just deletes or updates too? –  dartonw Jun 5 at 9:12
    
Thanks - it's just deletes, your RowCount fixes this (from my so far limited testing). In retrospect, my original question is obviously Yes, the Identity value is in the Inserted table when running an Insert statement. –  SteB Jun 6 at 9:33

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.