More questions on triggers

#1

1. Where are triggers shown on the Object Explorer in SSMS? I
thought they would be listed under "Programmability ... Database
Triggers" but they aren't there.

2. Is there a GUI to create a trigger, like there is to create a
table? I've been using straight SQL for this.

3. How do you modify a trigger? I've been using sp_helptrigger to
get the trigger name (since I can't find it anywhere in SSMS), and
then sp_helptext to get the SQL of the trigger.

Thanks, Dom

#2

On 27-04-2011 15:02, Dom wrote:

Quote:

1. Where are triggers shown on the Object Explorer in SSMS? I
thought they would be listed under "Programmability ... Database
Triggers" but they aren't there.
That's where they are listed. Did you d a refresh after creating the

trigger?

Quote:

2. Is there a GUI to create a trigger, like there is to create a
table? I've been using straight SQL for this.
Don't think so, but using straight SQL for this is a good idea anyway.

3. How do you modify a trigger? I've been using sp_helptrigger to
get the trigger name (since I can't find it anywhere in SSMS), and
then sp_helptext to get the SQL of the trigger.

alter trigger <triggername> ON DATABASE FOR <ddl_action> AS
print 'this is a database trigger'

See also my answer to 1.

Quote:

Thanks, Dom

Best,
Henk

#3

On Apr 27, 9:16*am, Henk van den Berg <hvandenb... (AT) xs4all (DOT) nl> wrote:

Quote:

On 27-04-2011 15:02, Dom wrote:> 1. *Where are triggers shown on the Object Explorer in SSMS? *I
thought they would be listed under "Programmability ... Database
Triggers" but they aren't there.

That's where they are listed. Did you d a refresh after creating the
trigger?

2. *Is there a GUI to create a trigger, like there is to create a
table? *I've been using straight SQL for this.

Don't think so, but using straight SQL for this is a good idea anyway.

3. *How do you modify a trigger? *I've been using sp_helptrigger to
get the trigger name (since I can't find it anywhere in SSMS), and
then sp_helptext to get the SQL of the trigger.

alter trigger <triggername> ON DATABASE FOR <ddl_action> AS
print 'this is a database trigger'

See also my answer to 1.

Thanks, Dom

Best,
Henk

Yes, I did a refresh on the "Database Triggers", and each of the
higher nodes, ("Programmability", etc.). Nothing shows up there. I
guess of all my questions, you've answered all, but I still don't see
a list of the triggers. Any ideas?

Dom

#4

On 27-04-2011 15:02, Dom wrote:

Quote:

3. How do you modify a trigger? I've been using sp_helptrigger to
get the trigger name (since I can't find it anywhere in SSMS), and
then sp_helptext to get the SQL of the trigger.

For more info on triggers:

#5

On 27-04-2011 15:02, Dom wrote:

Quote:

3. How do you modify a trigger? I've been using sp_helptrigger to
get the trigger name (since I can't find it anywhere in SSMS), and
then sp_helptext to get the SQL of the trigger.

For more info on triggers see Books OnLine at:

ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/cefa4fc4-b8b9-4cd7-b124-eed5283acbfc.htm

#6

On 27-04-2011 15:26, Dom wrote:

Quote:

Yes, I did a refresh on the "Database Triggers", and each of the
higher nodes, ("Programmability", etc.). Nothing shows up there. I
guess of all my questions, you've answered all, but I still don't see
a list of the triggers. Any ideas?

Just to cover all the bases: when you close SSMS and then re-open it, do
they show up?

#7

On Apr 27, 9:30*am, Henk van den Berg <hvandenb... (AT) xs4all (DOT) nl> wrote:

Quote:

On 27-04-2011 15:26, Dom wrote:

Yes, I did a refresh on the "Database Triggers", and each of the
higher nodes, ("Programmability", etc.). *Nothing shows up there. *I
guess of all my questions, you've answered all, but I still don't see
a list of the triggers. *Any ideas?

Just to cover all the bases: when you close SSMS and then re-open it, do
they show up?

Nope. I'm really stumped on this.

#8

On Apr 27, 9:34*am, Dom <dolivas... (AT) gmail (DOT) com> wrote:

Quote:

On Apr 27, 9:30*am, Henk van den Berg <hvandenb... (AT) xs4all (DOT) nl> wrote:

On 27-04-2011 15:26, Dom wrote:

Yes, I did a refresh on the "Database Triggers", and each of the
higher nodes, ("Programmability", etc.). *Nothing shows up there. *I
guess of all my questions, you've answered all, but I still don't see
a list of the triggers. *Any ideas?

Just to cover all the bases: when you close SSMS and then re-open it, do
they show up?

Nope. *I'm really stumped on this.

Also, as I've indicated before, sp_helptrigger, and sp_helptext work
as expected, so they are getting stored properly.

#9

On 27-04-2011 15:34, Dom wrote:

Quote:

Nope. I'm really stumped on this.

That makes two of us.
Is reinstalling the client tools an option?

#10

Dom wrote:

Quote:

1. Where are triggers shown on the Object Explorer in SSMS? I
thought they would be listed under "Programmability ... Database
Triggers" but they aren't there.

No, they're not there. They are listed within each table node, along with
the columns,keys, constraints and indexes.
I guess the thinking is that they are table objects, not database objects,
so just as you don't have a node to show all the indexes in the database,
neither do you have a node to show all triggers. You have to expand the
individual table nodes.

Quote:

2. Is there a GUI to create a trigger, like there is to create a
table? I've been using straight SQL for this.

No. If you right-click a trigger in the Triggers node and select New
Trigger, it will open a query window with the script from a trigger-creation
template.

Quote:

3. How do you modify a trigger? I've been using sp_helptrigger to
get the trigger name (since I can't find it anywhere in SSMS), and
then sp_helptext to get the SQL of the trigger.

Right-click a trigger in the Triggers node and select Modify