Microsoft KB Archive/114677

FIX: Trigger Still Fires if NULL Put in NOT NULL Column

PSS ID Number: Q114677 Article last modified on 10-31-1994

4.20

WINDOWS

BUG# NT: 638 (4.2)

= SYMPTOMS =

If a stored procedure attempts to insert or update a row in a table with a corresponding trigger, and the row violates the schema by putting a NULL into a NOT NULL column, the appropriate trigger will still fire and clear the @@error variable so that it cannot be captured in the stored procedure. There is no way of knowing that the INSERT or UPDATE failed.

= WORKAROUND =

Capture the error in the trigger and return it to the calling stored procedure using RAISEERROR.

= STATUS =

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

= MORE INFORMATION =

If any other error occurs, such as a rule violation, the trigger does not fire. If the same commands are put into a script, the trigger does not fire.

Additional reference words: 4.20 kernel NULL trigger @@error Windows NT KBCategory: kbprg KBSubcategory: SSrvWinNT

=
================================================================

Copyright Microsoft Corporation 1994.