BUG: Bad Trigger Generated for Table with Nullable Foreign Key

ID: Q122256

The information in this article applies to:
BUG# 10388 (4.2)

SYMPTOMS

Incorrect SQL is generated if Object Manager (OM) is used to generate an insert trigger on a table containing a nullable foreign key.

The code generated will be similar to: 


   IF( SELECT COUNT(*) FROM master, inserted WHERE inserted.id = master.id
   OR inserted.id IS NULL ) <> ( SELECT COUNT(*) FROM inserted )
   begin
       raiserror
       rollback transaction
   end

This is incorrect since the clause to the right of the OR is an open join with the master table. Thus, it will result in a count of all records in master for each row with NULL row being added.


WORKAROUND

A trigger to correctly check primary key/foreign key relationships can be created using the following syntax: 


   IF (SELECT COUNT(*) FROM master, inserted WHERE inserted.id = master.id)
   <> (SELECT COUNT(*) FROM inserted WHERE inserted.id IS NOT NULL )
   BEGIN
       /* error handling code */ 
   END


STATUS

Microsoft has confirmed this to be a problem in SQL Object Manager version 4.2. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

Additional query words: Windows NT 


Keywords          : kbprg kbtool SSrvObj_Man kbbug4.20 SSrvWinNT 
Version           : 4.2
Platform          : WINDOWS 
Issue type        :

Last Reviewed: March 20, 1999