Microsoft KB Archive/832964

= BUG: Parent referential integrity triggers are not generated when you use Visio to generate a SQL Server 2000 database =

Article ID: 832964

Article Last Modified on 1/8/2004

-

APPLIES TO


 * Microsoft Visio for Enterprise Architects 2002
 * Microsoft SQL Server 2000 Standard Edition

-





SYMPTOMS
If you generate a Microsoft SQL Server 2000 database by using Microsoft Visio, and if the database properties for referential action are as follows:
 * The On parent update property is set to No action
 * The On parent delete property is set to Set NULL

you may notice that the parent referential integrity triggers are not generated.



STATUS
Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article.



WORKAROUND
To work around this problem, explicitly create the for update and for delete trigger on the parent table with the appropriate functionality.



Steps to Reproduce the Behavior
 Start Visio. Create a new database model diagram. To set the database driver to SQL Server 2000, follow these steps:  On the Database menu, point to Options, and then click Drivers. In the Default driver for Visio list, click Microsoft SQL Server, and then click OK.</ol> </li>  Create two tables with the following definition: <pre class="fixed_text"> Table Name - Emp:

Column Name    Data Type empid          int Primary Key empname        char(10) deptid         int Table Name - Dept:

Column Name    Data Type deptid         int Primary Key deptName       char(10) </li> Create a relationship on the deptid column with Emp as the child table and Dept as the parent table.</li> In the Database Properties for the relationship, in the Referential Action dialog box, click No action under On parent update.</li> Under On parent delete, click Set NULL.</li> On the Database menu, click Generate.</li> In the Generate Wizard dialog box, click Next.</li> In the Database name box, type test, and then click Finish.</li> In the SQL Server Create Database dialog box, click Close.

You receive the following message:

Do you want to view the generated DDL script?

</li> Click Yes to view the Data Definition Language (DDL) script.

You notice that two triggers, for insert and for update, are created on the Emp table. However, no triggers are defined on the Dept table.

</li></ol>

You may also notice the behavior that is mentioned in the &quot;Symptoms&quot; section of this article when you perform any one of the following tasks:
 * You try to verify the triggers that are generated on the child and parent table by using SQL Server Enterprise Manager.
 * You try to verify the action of the triggers by following these steps:
 * Enter data in the child and parent table.
 * Delete rows from the parent table.

Notice that the referencing child rows are not updated to have the Foreign Key property set to null).
 * You reverse engineer the new database into a new diagram, and then you check the DDL script of the new diagram.

Keywords: kbbug kbtrigger KB832964

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.