Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to find all trigger associated with a table with SQL Server?

I created a trigger for a table in SQL Server and it works for me.

My problem is: How do find it and modify it?

I use this query to find my triggers:

select * from sys.triggers 

This find all triggers, but how to open it and change the triggers?

like image 636
ar.gorgin Avatar asked Sep 10 '12 07:09

ar.gorgin


People also ask

How can I see all table triggers in SQL Server?

To view database level triggers, Login to the server using SQL Server management studio and navigate to the database. Expand the database and navigate to Programmability -> Database Triggers. To view triggers at the server level, Login to Server using SSMS and navigate to Server Objects and then Triggers folder.

Is a trigger associated with a specific table?

Triggers on DML StatementsDML triggers for event publication are associated with a table. They can be either BEFORE or AFTER triggers that fire for each row on which the specified DML operation occurs.

Where triggers are stored in SQL Server?

Server-scoped DDL triggers appear in the SQL Server Management Studio Object Explorer in the Triggers folder. This folder is located under the Server Objects folder. Database-scoped DDL triggers appear in the Database Triggers folder.


2 Answers

You can do this simply with SSMS. Just go to your table name and expand the Triggers node to view a list of triggers associated with that table. Right click to modify your trigger. enter image description here

like image 127
Buzz Avatar answered Oct 04 '22 10:10

Buzz


select so.name, text from sysobjects so, syscomments sc where type = 'TR' and so.id = sc.id and text like '%YourTableName%' 

This way you can list out all the triggers associated with the given table.

like image 39
Jigar Pandya Avatar answered Oct 04 '22 10:10

Jigar Pandya