BUG: Bad Trigger Generated for Table with Nullable Foreign Key

ID: Q122256

The information in this article applies to:

Microsoft SQL Server Programmer's Toolkit, version 4.2

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 :