Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How can I view the code on a trigger?

Posted on 2007-12-04
7
Medium Priority
?
218 Views
Last Modified: 2010-04-21
Lest asume that I have used the following syntax to find the name of a trigger in Computers DB and  I need to modify it.
Select * From [Computers].dbo.sysobjects Where xtype = 'TR'

Also, asume that I don't know were the code file resides for the trigger.

How can I get to view the code on this trigger? Is there a sql syntax for this?

I am using SQL Server Express.
0
Comment
Question by:vielkacarolina1239
  • 3
  • 2
  • 2
7 Comments
 
LVL 3

Expert Comment

by:Martin-Smith
ID: 20402802
sp_help 'triggername'
0
 
LVL 3

Assisted Solution

by:Martin-Smith
Martin-Smith earned 500 total points
ID: 20402846
Sorry that doesn't work.

select text from syscomments where id = object_id('triggerName') does but you may need to concatenate if multipe rows are returned
0
 
LVL 7

Accepted Solution

by:
bungHoc earned 1500 total points
ID: 20402871
sp_helptext 'dbo.nameOfTrigger' -- assuming you know the name.

If you don't know the name..  well.. this will help you find all  related to one table.
SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so on sc.id=so.id
WHERE sc.text LIKE '%tablename%'
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

Author Comment

by:vielkacarolina1239
ID: 20402905

I ran the syntax:  sp_help 'myTrgger'

The Result pane returns the Name of the trigger, Woner, Type, Create_Date.

However, I need to see the code on this trigger. When I run the above syntax, is there a pane I need to open to view and alter the code on this trigger?

Thanks
0
 
LVL 7

Assisted Solution

by:bungHoc
bungHoc earned 1500 total points
ID: 20402961
Anyway.. for every table, when you click to expand, you'll see a bunch of folders Columns, Keys, Constraints, Indexes, Statistics and..... Triggers.

If you have right permissions, you should see triggers.
If they're not encrypted.. view it..
0
 
LVL 3

Expert Comment

by:Martin-Smith
ID: 20402981
I told you wrong . bungHoc got it right with sp_helptext
0
 

Author Closing Comment

by:vielkacarolina1239
ID: 31412579
Thanks guys, you are great. Both solutions work. buhgHoc solution is a little cleaner than marting. But, both work.
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.

885 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question