Error trying to insert data using trigger T-SQL

Question

Below is a trigger used on one of our SQL tables for any insert/update action. 99/100 times this trigger works just fine however every now and then we receive this error message:

Cannot insert the value NULL into column 'TransactionDate', table 'AgentResourcesU01.dbo.TransactionLog'; column does not allow nulls.
INSERT fails. The statement has been terminated.

As you can see from the Insert statement, the columns in our transaction log table are TransactionDate, Operator, TableName, Action, TableString and UserId. I set the variable @transDate in the opening SELECT statement so as it appears to me, there should be no way a NULL gets in there unless it's bad data coming in.

Any thoughts?

    BEGIN
        SELECT @symetraNumber = SymetraNumber, @lastChangeOperator = LastChangeOperator, @transDate = LastChangeDate, @entityType = EntityType, 
        @firstName = FirstName, @lastName = LastName, @suffix = NameSuffix, @corpName = CorporateName, @controlId = ControlId
        FROM inserted

        IF @firstName IS NULL SET @firstName = 'NULL'
        IF @lastName IS NULL SET @lastName = 'NULL'
        IF @suffix IS NULL SET @suffix = 'NULL'
        IF @corpName IS NULL SET @corpName = 'NULL'
        IF @controlId IS NULL SET @controlId = 'NULL'

        SET @tableString = 'SymNum:' + @symetraNumber + ' EntType:' + @entityType + ' Fname:' + @firstName + ' Lname:' + @lastname + ' Suff:' + @suffix +
            ' CorpName:' + @corpName + ' ctrlId:' + @controlId

        INSERT INTO TransactionLog (TransactionDate, Operator, TableName, Action, TableString, UserId)
        VALUES (@transDate, 'Op', @tableName, @action, @tableString, @lastChangeOperator)
    END

You main problem is this: you seem to assume that the Inserted table only ever contains a single row. That assumption is wrong. The trigger fires once per batch - e.g. if you insert 50 rows at once, your trigger fires once and the Inserted table will contain 50 rows - so your statements like SELECT @symetraNumber = SymetraNumber .. FROM inserted will fail miserably .... — marc_s
– marc_s, Commented Sep 27, 2012 at 18:34
Instead of inserting using VALUES use a SELECT statement from inserted. — GilM
– GilM, Commented Sep 27, 2012 at 19:02

Aaron Bertrand · Accepted Answer · 2012-09-27 19:06:21Z

To demonstrate Marc's point, you can do this in a set-based way, without all these nasty variables and IF checks:

INSERT dbo.TransactionLog
(
  TransactionDate, 
  Operator, 
  TableName, 
  Action,
  TableString,
  UserId
)
SELECT 
  LastChangeDate, 
  'Op',
  @TableName,
  @action, 
     'SymNum:'      + COALESCE(SymetraNumber, 'NULL')
     + ' EntType:'  + COALESCE(EntityType,    'NULL')
     + ' Fname:'    + COALESCE(FirstName,     'NULL')
     + ' Lname:'    + COALESCE(LastName,      'NULL')
     + ' Suff:'     + COALESCE(NameSuffix,    'NULL')
     + ' CorpName:' + COALESCE(CorporateName, 'NULL')
     + ' ctrlId:'   + COALESCE(ControlId,     'NULL'),
  LastChangeOperator
FROM inserted;

If LastChangeDate in the underlying table is NULLable, either mark it as NOT NULL, fix the problem where NULL is getting inserted, or both. The trigger shouldn't have to know about this constraint but you can work around it by doing something like this (if the value is NULL, set it to right now):

...
  UserId
)
SELECT 
  COALESCE(LastChangeDate, CURRENT_TIMESTAMP), 
  'Op',
...

Bogdan Sahlean · Accepted Answer · 2012-09-27 19:21:15Z

[1] I assume that you have an INSERT/UPDATE/DELETE trigger and when somebody try to delete rows from the base table then the inserted table from trigger will have zero rows. Look at this example:

CREATE TABLE MyTableWithTrigger (
    MyID INT IDENTITY PRIMARY KEY,
    LastUpdateDate DATETIME NOT NULL
);
GO

CREATE TABLE TransactionLog (
    TransactionLogID INT IDENTITY PRIMARY KEY,
    CreateDate DATETIME NOT NULL DEFAULT GETDATE(),
    LastUpdateDate DATETIME NOT NULL,
    MyID INT NOT NULL
);
GO

CREATE TRIGGER trIUD_MyTableWithTrigger_Audit
ON MyTableWithTrigger
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    DECLARE @LastUpdateDate DATETIME, @MyID INT;
    SELECT @LastUpdateDate=i.LastUpdateDate, @MyID=i.MyID
    FROM inserted i;

    INSERT TransactionLog (LastUpdateDate, MyID)
    VALUES (@LastUpdateDate, @MyID) 
END;
GO

PRINT 'Test 1'
INSERT  MyTableWithTrigger (LastUpdateDate)
VALUES  ('2011-01-01');
DELETE MyTableWithTrigger;
SELECT * FROM MyTableWithTrigger;
SELECT * FROM TransactionLog;

Output:

Msg 515, Level 16, State 2, Procedure trIUD_MyTableWithTrigger_Audit, Line 10
Cannot insert the value NULL into column 'LastUpdateDate', table 'Test.dbo.TransactionLog'; column does not allow nulls. INSERT fails.
The statement has been terminated.

[2] To correct your trigger (see Marc's comment) please read pages 192 - 199 from Alex Kuznetsov's book (Defensive Database Programming with SQL Server: Amazon, Red Gate - PDF).

Collectives™ on Stack Overflow

Error trying to insert data using trigger T-SQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related