BUG: Bad Trigger Generated for Table with Nullable Foreign Key

• Microsoft SQL Server Programmer's Toolkit, version 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

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.