BUG: SEM script generation order not proper

ID: Q136865

The information in this article applies to: BUG# NT: 11395

SYMPTOMS

In the Microsoft SQL Enterprise Manager (SEM) scripting options, if you include triggers along with the tables, the order in which the scripts are generated may not be correct in some cases.

WORKAROUND

Generate scripts for triggers separately, and run them only after scripts for other objects have been run.

STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.0. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

The order in which scripts are generated is as follows: a table, all its triggers, next table (in alphabetical order), all its triggers, and so forth.

Suppose there are two tables, TBL1 and TBL2, and a trigger, TRIG1, on TBL1. Assume that TRIG1 does an update on TBL2. The SQL script generated is in the following order: 

   CREATE TABLE TBL1...
   CREATE TRIGGER TRIG1... (this trigger updates TBL2)
   CREATE TABLE TBL2...
If you run this script in a different database, an error occurs on the CREATE TRIGGER because TBL2 does not yet exist. All CREATE TRIGGER statements should appear after all the CREATE TABLE statements.

For example: 

   use pubs
   go
   create table tbl1(x int)
   create table tbl2(x int)
   go
   create trigger trig1 on tbl1 for update as
   update table tbl2 set x = 100
   go
   print 'Now generate script for tbl1, tbl2 and trig1 through SEM and run'
   print 'it in a different database.'
Additional query words: sql6 ent mgr batch 
Keywords          : kbprg SSrvEntMan SSrvProg kbbug6.00 
Version           : 6.0
Platform          : WINDOWS

Last Reviewed: May 2, 1997