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


The information in this article applies to:

  • Microsoft Visio for Enterprise Architects (2002)
  • Microsoft SQL Server 2000 (all editions)

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.

MORE INFORMATION

Steps to Reproduce the Behavior

  1. Start Visio.
  2. Create a new database model diagram.
  3. To set the database driver to SQL Server 2000, follow these steps:
    1. On the Database menu, point to Options, and then click Drivers.
    2. In the Default driver for Visio list, click Microsoft SQL Server, and then click OK.
  4. Create two tables with the following definition:
    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)

  5.  Create a relationship on the deptid column with Emp as the child table and Dept as the parent table.
  6.  In the Database Properties for the
				relationship, in the Referential Action dialog box, click No action under On parent update.
				
  7. Under On parent delete, click Set NULL.
  8. On the Database menu, click
				Generate.
  9. In the Generate Wizard dialog box, click
				Next.
  10. In the Database name box, type
				 test, and then click Finish.
  11. In the SQL Server Create Database dialog
				box, click Close.

    You receive the following
				message:
    Do you want to view the generated DDL
				script?
  12. 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.

You may also notice the behavior that is mentioned in the
		  "Symptoms" 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: 
				
    1. Enter data in the child and parent table.
    2. 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.
Modification Type:MajorLast Reviewed:1/8/2004
Keywords:kbTrigger kbbug KB832964 kbAudDeveloper

                  

                

              		
            

          

          

            

              

                

                  
                    
©2003 Microsoft Corporation. All rights reserved.