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
 

----------------------------------------------------------------------
The information in this article applies to:
 
  - Microsoft SQL Server version 4.2
----------------------------------------------------------------------
 
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.
