## FIX: Inserting NULL into NOT NULL Column May Cause AV

Q122574

The information in this article applies to:

- Microsoft SQL Server version 4.2x

BUG# NT: 904 (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)

The first time this trigger is fired, it causes error 515 (as expected):

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

If the trigger is fired again, an access violation may occur.
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.

Additional query words: sql6 Windows NT

Keywords : kbother kbprogramming
Issue type : kbbug
Technology : kbSQLServSearch kbAudDeveloper kbSQLServ420OS2

Last Reviewed: March 24, 2000 © 2001 Microsoft Corporation. All rights reserved. Terms of Use.