INF: Using SQL Server Web Assistant's "When Data Changes" Option

ID: Q168118

The information in this article applies to:

Microsoft SQL Server version 6.5

SUMMARY

This article describes how the SQL Server Web Assistant handles HTML page generation using the "When Data Changes" scheduling option.

MORE INFORMATION

With the SQL Server Web Assistant, you can generate an HTML file whenever the data changes for one or more tables, by using the Scheduling screen and selecting the "When Data Changes" option. This is accomplished by building an INSERT, UPDATE, and DELETE trigger for each of the tables selected by the user. Any existing triggers will be automatically detected by the Web Assistant and retained.

A "Trigger Found" dialog box will be raised for each of the existing triggers for all of the tables referenced in the Scheduling screen. The user has the option of editing the trigger code and clicking OK to accept the proposed changes, or clicking Cancel to leave the trigger intact.

The trigger object built by the SQL Web Assistant will have a name generated according to the following:

Web_tableObjectId_1 -> INSERT trigger
Web_tableObjectId_2 -> UPDATE trigger
Web_tableObjectId_3 -> DELETE trigger

For example, if the "authors" table from the pubs database is selected as one of the tables to be considered when the data changes, the three triggers generated by the Web assistant will be: Web_16003088_1, Web_16003088_2 and Web_16003088_3 for the INSERT, UPDATE, and DELETE respectively (where 16003088 is the object ID corresponding to the authors table in the pubs database).

Please note that "sp_depends" does not enlist any of the Web Assistant generated triggers for a given table. You may want to create an enhanced version of "sp_depends" to enlist the Web Assistant generated triggers as discussed in the following article in the Microsoft Knowledge Base:

Q167135 : A Stored Procedure to Display Trigger Information

Use the following steps to drop any of the triggers generated by the Web Assistant:

Identify the object ID for the table in question:
```
      select tabObjId=object_id('tabName')
 
```
You can also use the sp_helptriggers stored procedure, as discussed in article Q167135, mentioned above.

List all the triggers for this table object:


      select * from sysobjects where name like 'Web_tabObjId%'

Run the Drop Trigger command for each of the triggers you want to drop:
```
      drop trigger <Webtriggername>
  
```

Keywords : kbcode kbusage SSrvStProc SSrvWeb
Version : 6.5
Platform : WINDOWS
Issue type : kbhowto