• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 352
  • Last Modified:

How to check triggers and edit them

Hi

I have 3 trigger which have to be updated. I can see then when
sp_help triggername.

How do I  view edit delete them
0
Richards
Asked:
Richards
  • 3
  • 3
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Using Enterprise Manager (SQL Server 2000 or lower), you go to the table, right-click, All Tasks->Manage Triggers.
the form opens and show the defaul "new trigger".
in the combo box however you will find the other triggers, select them, and the code will appear in the code box...
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Using Query analyser, u can run the procedure

exec sp_helpText 'urTriggerName'


copy the result and paste it in another window, change "CREATE' to 'ALTER'  do ur modifications, run it



Method2

Use QA -> Press F8 -> expand ur table -> triggers
right click on the trigger and select edit the contents in a new window
0
 
RichardsAuthor Commented:
I am using sql server 2000
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Aneesh RetnakaranDatabase AdministratorCommented:
both of the above posts are for SQL server 2000
0
 
RichardsAuthor Commented:
Thanks when I change some SQL s inside the trigger and run it I am getting the below error. Do I need to drop and create it ?

Server: Msg 2714, Level 16, State 5, Procedure  trgName, Line 65535
There is already an object named 'trgName' in the database.



 CREATE  TRIGGER trgName
ON tableName  
AFTER insert, update  
 
AS  

 DECLARE @var int  
.
.
.
/*
Commented some sqls
*/


0
 
Aneesh RetnakaranDatabase AdministratorCommented:
replace  "CREATE  " with  " ALTER " 



ALTER  TRIGGER trgName
ON tableName  
AFTER insert, update  
 
AS  

 DECLARE @var int  
.
.
.
/*
Commented some sqls
*/

0
 
RichardsAuthor Commented:
do I need to commit
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
no.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now