Scripting of database objects in SQL 2005

30 Dec 2005 8:28 PM

Edward

I've written scripts to generate the tables, functions, indexes, keys, views
and stored procedures but I can't find any information for triggers. The
sys.triggers table contains no entries even though I've created a trigger.
When I script from the SQL Management Studio it works fine. I monitored the
process in the profiler and copied the commands out, but all return no
results.

Has anyone queried the database for the creation scripts for triggers? Am I
querying the wrong tables? I've looked in sys.objects but see no TR types.

30 Dec 2005 10:51 PM

Erland Sommarskog

Edward (Edw***@discussions.microsoft.com) writes:

> I've written scripts to generate the tables, functions, indexes, keys,
> views and stored procedures but I can't find any information for
> triggers. The sys.triggers table contains no entries even though I've
> created a trigger. When I script from the SQL Management Studio it
> works fine. I monitored the process in the profiler and copied the
> commands out, but all return no results.
>
> Has anyone queried the database for the creation scripts for triggers?
> Am I querying the wrong tables? I've looked in sys.objects but see no
> TR types.

Triggers are not in sys.objects, but they are in sys.sql_modules and
sys.triggers (except for server triggers). This script demonstrates:

   use master
   go
   create database triggertest
   go
   use triggertest
   go
   CREATE TABLE rune (a int NOT NULL)
   go
   CREATE TRIGGER rune_tri ON rune FOR INSERT AS
      Print 'Trigging!'
   go
   SELECT * FROM sys.triggers
   SELECT * FROM sys.sql_modules
   SELECT object_definition(object_id('rune_tri'))
   go
   use master
   go
   DROP DATABASE triggertest

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

30 Dec 2005 11:04 PM

Edward

Thanks. It turns out it was a bug in SQL Management Studio. I remoted into
the server, connected with DAC, queried sysobjects and BLAM. They're showing
up in sys.triggers and on all other machines after the MS is exited and
restarted on those machines.

31 Dec 2005 9:51 AM

Erland Sommarskog

Edward (Edw***@discussions.microsoft.com) writes:

> Thanks. It turns out it was a bug in SQL Management Studio. I remoted
> into the server, connected with DAC, queried sysobjects and BLAM.
> They're showing up in sys.triggers and on all other machines after the
> MS is exited and restarted on those machines.

Note that I understand how that bug would look like, but did you report
the bug on http://lab.msdn.microsoft.com/productfeedback/default.aspx?

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx