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

Question

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

User · Answer

You Can View All trigger related to your database by below query  select   from sys triggers   And for open trigger you can use below syntax  sp helptext  dbo trg InsertIntoUserTable

User · Answer

find triggers on table   select so name  text from sysobjects so  syscomments sc where type    TR  and so id   sc id and text like   TableName     and you can find store procedure which has reference of table   SELECT Name FROM sys procedures WHERE OBJECT DEFINITION OBJECT ID  LIKE   yourtablename

User · Answer

use sp helptrigger to find the triggerlist for the associated tables

User · Answer

With this query you can find all Trigger in all tables and all views        WITH         TableTrigger         AS                       Select                  Object Kind    Table                   Sys Tables Name As TableOrView Name                    Sys Tables Object Id As Table Object Id                   Sys Triggers Name As Trigger Name                   Sys Triggers Object Id As Trigger Object Id              From Sys Tables              INNER Join Sys Triggers On   Sys Triggers Parent id   Sys Tables Object Id               Where   Sys Tables Is MS Shipped   0                      ViewTrigger         AS                       Select                  Object Kind    View                   Sys Views Name As TableOrView Name                    Sys Views Object Id As TableOrView Object Id                   Sys Triggers Name As Trigger Name                   Sys Triggers Object Id As Trigger Object Id              From Sys Views              INNER Join Sys Triggers On   Sys Triggers Parent id   Sys Views Object Id               Where   Sys Views Is MS Shipped   0                      AllObject         AS                       SELECT   FROM TableTrigger              Union ALL              SELECT   FROM ViewTrigger                 Select                 From AllObject     Order By Object Kind  Table Object Id

User · Answer

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

User · Answer

Try to Use   select   from sys objects where type  tr  and name like    Insert

User · Answer

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

User · Answer

select   from information schema TRIGGERS

User · Answer

This might be useful   SELECT   t name AS TableName   tr name AS TriggerName   FROM sys triggers tr INNER JOIN sys tables t ON t object id   tr parent id WHERE  t name in   TABLE NAME S  GOES HERE      This way you just have to plugin the name of tables and the query will fetch all the triggers you need

User · Answer

select o1 name as trigger name o2 name as table name from sys objects o1   join sys objects o2 on   o1 parent object id o2 object id       where o1 type  TR    and o2 name  my table

User · Answer

select m definition from sys all sql modules m inner join  sys triggers t on m object id   t object id    Here just copy the definition and alter the trigger   Else you can just goto SSMS and Expand the your DB and under Programmability expand Database Triggeres then right click on the specific trigger and click modify there also you can change

User · Answer

Much simple query below  select  select  name  from  sys tables where  object id    tr parent id   as TableName     from sys triggers tr

User · Answer

Go through   Need to list all triggers in SQL Server database with table name and table  39 s schema  This URL  have set of queries by which you can get the list of triggers associated with  particular table   I believe you are working in sqlserver following are the steps to get modify triggers   To modify a trigger   Expand a server group  and then expand a server  Expand Databases  expand the database in which the table containing the trigger belongs  and then click Tables  In the details pane  right-click the table on which the trigger exists  point to All Tasks  and then click Manage Triggers  In Name  select the name of the trigger  Change the text of the trigger in the Text field as necessary  Press CTRL TAB to indent the text of a SQL Server Enterprise Manager trigger  To check the syntax of the trigger  click Check Syntax

User · Answer

select t name as TriggerName m definition is disabled  from sys all sql modules m  inner join   sys triggers t on m object id   t object id  inner join sys objects o on o object id   t parent id Where o name    YourTableName    This will give you all triggers on a Specified Table

User · Answer

you can open your trigger with sp helptext yourtriggername

[sql-server] How to find all trigger associated with a table with SQL Server?

Examples related to sql-server

Examples related to sql-server-2008