Microsoft KB Archive/122256

{|
 * width="100%"|

BUG: Bad Trigger Generated for Table with Nullable Foreign Key

 * }

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 : kbprogramming kbtool

Issue type :

Technology : kbSQLServSearch kbAudDeveloper kbSQLServPTK420