Trigger AFTER INSERT results can't insert NULL in log table

Question

I am trying to write a trigger after insert for the table called PersonalInfo. GenderID and id are foreign keys from tables Gender and RegisterationForm.

And DBLog is the table I use to log the trigger actions.

This is what I wrote:

Create Trigger [dbo].[InsertPersonalInfoTrigger] ON [dbo].[PersonalInfo]
after Insert
as
begin
declare @Name nvarchar(50)
declare @FamilyName nvarchar(50)
declare @FatherName nvarchar(50)
declare @BirthDate nchar(10)
declare @GenderID int
declare @NationalId nvarchar(50)
declare @id int


select @Name=Max(@Name),@FamilyName=Max(@FamilyName),@FatherName=Max(@FatherName),@BirthDate=Max(@BirthDate)
,@GenderID=Max(@GenderID),@NationalId=Max(@NationalId),@id=Max(@id)
    from  Inserted 
if(@Name='')
begin
    RAISERROR ('Enter the Name', 
           16, 
           1   
           ); 
    RollBack   
    return;
end
declare @LogID int
set @LogID= (select isnull(max(LogID),0) from DBLog) +1

declare @ActionDes varchar(2000);
set @ActionDes = 'Insert Into Gender INNER JOIN
                  PersonalInfo ON Gender.GenderID = PersonalInfo.GenderID INNER JOIN
                  RegisterationForm ON PersonalInfo.id = RegisterationForm.id(Name,FamilyName,FatherName,BirthDate,GenderID,NationalId,id) 
                  Values("' + @Name + '","' + @FamilyName + '","' + @FatherName + '","' + @BirthDate + '",'+cast(@GenderID as varchar(20))+ ',
                  "' + @NationalId +'",'+cast(@id as varchar(20))+ ')';
declare @dt datetime;
set @dt=getdate();

declare @usr varchar(50);
select @usr =current_user  

insert into DBLog(LogID,ActionDes,ActionTime,ActionUser) 
    values (@LogID,@ActionDes,@dt,@usr)
end
Go

after executing the trigger when I try to insert data to my PersonalInfo table I get the following error:

No row was updated

The data in row 5 was not comitted

Error Source: .Net SqlClient Data Provider.

Error Message: Cannot insert the value NULL into column 'ActionDes', table '.dbo.DBLog'; column does not allow nulls.INSERT fails.

Warning: Null value is eliminated by an aggregate or other SET operation.

The statement has been terminated.

It's true that I'm not allowing NULLs into my ActionDes column, but here I'm inserting data. Where does the NULL come from? What's the problem?

Answer 1

Don't store a string making up a "pretend" insert statement. There are multitudes of ways to insert into a table, you're guessing at the format used, that's a bad idea.

You're also assuming that only one row is being inserted. That's a heavily flawed assumption, recode to read multiple rows from the inserted table.

Allow NULLs in the logging. If the name field shouldn't be NULL in the [personalInfo] table, make the field NOT NULL, don't use a trigger to enforce a check constraint.

Don't read from the logging table to get an id and then increment the value, make the id column an IDENTITY column and let the database manage that itself. You have a race condition where you could read the value, then some other process insert a row, then you try to insert your row and generate a conflict. It's a really bad practice.

CREATE [dbo].[InsertPersonalInfoTrigger] ON [dbo].[PersonalInfo]
  AFTER INSERT
AS
BEGIN

    INSERT INTO
      DBLog (
        ActionDes,
        ActionTime,
        ActionUser
      )
    SELECT
      ISNULL('"' + CAST(inserted.ID         AS NVARCHAR(128) + '"', 'NULL') + ' AS id, ' +
      ISNULL('"' + CAST(inserted.Name       AS NVARCHAR(128) + '"', 'NULL') + ' AS name, ' +
      ISNULL('"' + CAST(inserted.FamilyName AS NVARCHAR(128) + '"', 'NULL') + ' AS FamilyName, ' +
      ISNULL('"' + CAST(inserted.FatherName AS NVARCHAR(128) + '"', 'NULL') + ' AS FatherName, ' +
      ISNULL('"' + CAST(inserted.BirthDate  AS NVARCHAR(128) + '"', 'NULL') + ' AS BirthDate, ' +
      ISNULL('"' + CAST(inserted.GenderID   AS NVARCHAR(128) + '"', 'NULL') + ' AS GenderID, ' +
      ISNULL('"' + CAST(inserted.NationalId AS NVARCHAR(128) + '"', 'NULL') + ' AS NationalID',
      GETDATE(),
      CURRENT_USER
    FROM
      inserted
      -- There could be many rows in this table, don't pretend or assume there will only be one
END

GO

Better still, have separate logging fields for each field you're recording from [PersonalInfo]. Even if that means having a generic DBLog table and a separate DBLogPersonalInfo table.

CREATE [dbo].[InsertPersonalInfoTrigger] ON [dbo].[PersonalInfo]
  AFTER INSERT
AS
BEGIN

    INSERT INTO
        DBLog (
            ActionDes,
            ActionTime,
            ActionUser
        )
    VALUES (
      'INSERT INTO [dbo].[PersonalInfo]',
      GETDATE(),
      CURRENT_USER
    );

    INSERT INTO
        DBLogPersonalInfo (
            DBLogID,
            id,
            Name,
            FamilyName,
            FatherName,
            BirthDate,
            GenderID,
            NationalID
        )
    SELECT
      SCOPE_IDENTITY(),  -- The latest value from the IDENTITY column of `DBlog` (as created by THIS process).
      inserted.ID,
      inserted.Name,
      inserted.FamilyName,
      inserted.FatherName,
      inserted.BirthDate,
      inserted.GenderID,
      inserted.NationalID
    FROM
      inserted

    -- No need for `CAST` or `ISNULL`.
    -- Now you can even join DBLogPersonalInfo to PersonalInfo and compare changes (programatically)
    -- Simpler, more reliable, and more flexible...

END

GO

Answer 2

A couple of things:

1. Here is a good article on, "Warning: Null value is eliminated by an aggregate or other SET operation."
2. Your trigger is written AFTER INSERT, not AFTER UPDATE as noted.
3. And finally, you are assuming that all of your values (MAX()) FROM INSERTED will be non-null. That may not be true. Try aggregating @actionDes with an ISNULL(, N'') or COALESCE(,N'') around each of the variables and I think you'll get rid of the "Error Message: Cannot insert the value NULL into column 'ActionDes', table '.dbo.DBLog'; column does not allow nulls.INSERT fails."

I'm not sure why you're performing the aggregates on the INSERTED but what I've described I believe will address the issues you've mentioned.

Answer 3

If I'm understanding your trigger right, for each row in the inserted table, you're setting @variable = Max(@variable). Since you hadn't assigned a value to the variable, all of the variable values are still null.

Rules of concatenation require that concatentation with a null value equals a null value, thus your @ActionDes variable ends up with a null value if any of the parameter values are null, and is what is throwing the error when you try to insert into the DBLog table.