FIX: Inserting NULL into NOT NULL Column May Cause AV

• Microsoft SQL Server, version 4.21a

SYMPTOMS

When you try to insert a NULL into a NOT NULL column inside a trigger, an access violation may occur under certain situations. For example, consider the following INSERT statement to be executed inside a trigger:

   insert mytable select (select NULL)

   Attempt to insert the value NULL into column '%.*s', table '%.*s';
   column does not allow NULLS. Update fails

NOTE: This problem occurs only if you are trying to insert NULLs into a column that does not allow NULLs. A trigger should not be designed to perform an invalid operation.

WORKAROUND

There are two ways to avoid this problem:

• Redesign the trigger so that it does not insert NULLs into a column that does not allow NULLs.
• Remove the extra SELECT statement. For example:

        insert mytable select (NULL)
  

STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server version 4.21a. This problem was corrected in SQL Server version 6.0. For more information, contact your primary support provider.