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

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 350
  • 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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