CODEWITHSUNDEEP
sql-serversql-server-2008
ar.gorgin
ar.gorgin

Reputation: 5002

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?

Upvotes: 60

Views: 290608

Answers (17)

Marcello Miorelli
Marcello Miorelli

Reputation: 3668

This script is based on this answer's.

I have added all I needed for checking out my triggers. It gives the schema.table or schema.view name and it also generates the scripts for enabling and disabling the trigger.

You can also filter by table or view name.

set transaction isolation level read uncommitted 
 ;WITH
        TableTrigger
        AS
        (
            Select 
                Object_Kind = 'Table',
                object_schema_name(sta.Object_Id) + '.' 
                       +  sta.Name As TableOrView_Name , 
                sta.Object_Id As Table_Object_Id ,
                object_schema_name(stri.Object_Id) + '.' 
                       +  stri.Name As Trigger_Name, 
                stri.Object_Id As Trigger_Object_Id,
                CASE WHEN stri.is_disabled =0 THEN 'Enabled' 
                     ELSE 'Disabled' END As Trigger_Status,
                stri.is_not_for_replication,
                stri.is_instead_of_trigger,
                stri.create_date,
                stri.modify_date,
                ';DISABLE TRIGGER ' +  quotename(object_schema_name(stri.Object_Id)) 
         + '.' +  quotename(stri.Name)
         + ' ON ' +  quotename(object_schema_name(sta.Object_Id) ) 
         + '.' +  quotename (sta.Name ) as disable_trigger,
                ';ENABLE TRIGGER ' +  quotename(object_schema_name(stri.Object_Id)) 
         + '.' +  quotename(stri.Name)
         + ' ON ' +  quotename(object_schema_name(sta.Object_Id) ) 
         + '.' +  quotename (sta.Name ) as enable_trigger
                
            From Sys.Tables sta with(nolock)
            INNER Join Sys.Triggers stri with(nolock)
                     On ( stri.Parent_id = sta.Object_Id )
            Where ( sta.Is_MS_Shipped = 0 )
        ),
        ViewTrigger
        AS
        (
            Select 
                Object_Kind = 'View',
                sv.Name As TableOrView_Name , 
                sv.Object_Id As TableOrView_Object_Id ,
                stri.Name As Trigger_Name, 
                stri.Object_Id As Trigger_Object_Id,
                CASE WHEN stri.is_disabled =0 THEN 'Enabled' 
                     ELSE 'Disabled' END As Trigger_Status,
                stri.is_not_for_replication,
                stri.is_instead_of_trigger,
                stri.create_date,
                stri.modify_date,
                ';DISABLE TRIGGER ' +  quotename(object_schema_name(stri.Object_Id)) 
+ '.' +  quotename(stri.Name)
+ ' ON ' +  quotename(object_schema_name(sv.Object_Id) ) 
+ '.' +  quotename (sv.Name ) as disable_trigger,
                ';ENABLE TRIGGER ' +  quotename(object_schema_name(stri.Object_Id)) 
+ '.' +  quotename(stri.Name)
+ ' ON ' +  quotename(object_schema_name(sv.Object_Id) ) + '.' 
+  quotename (sv.Name ) as enable_trigger                                 
            From Sys.Views sv with(nolock)
            INNER Join Sys.Triggers stri with(nolock)
                    On ( stri.Parent_id = sv.Object_Id )
            Where ( sv.Is_MS_Shipped = 0 )
        ),
        AllObject
        AS
        (
            SELECT * FROM TableTrigger with(nolock)

            Union ALL

            SELECT * FROM ViewTrigger with(nolock)
        )


    Select 
        * 
    From AllObject with(nolock)
    --here you can specify one object (table or view)
    -- where Table_Object_Id = object_id('dbo.tblaccount',N'U')
    Order By Object_Kind, Table_Object_Id

Upvotes: 0

user8804754
user8804754

Reputation: 41

select   B.name 
from     sys.objects    A
join     sys.triggers   B
on     A.object_id    =    B.parent_id
where    A.name    ='Table_name' /*Table Name*/

Upvotes: 4

YakovGdl35
YakovGdl35

Reputation: 349

 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'

Upvotes: 2

danish
danish

Reputation: 21

you can open your trigger with sp_helptext yourtriggername

Upvotes: 0

Fezal halai
Fezal halai

Reputation: 784

Try to Use:

select * from sys.objects where type='tr' and name like '%_Insert%'

Upvotes: 0

Anuj K Dwivedi
Anuj K Dwivedi

Reputation: 29

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'

Upvotes: 1

Jigar Pandya
Jigar Pandya

Reputation: 5977

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.

Upvotes: 55

ℛⱥℐℰşℎ
ℛⱥℐℰşℎ

Reputation: 360

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

Upvotes: 0

Mrinal
Mrinal

Reputation: 125

select * from information_schema.TRIGGERS;

Upvotes: 0

Rahul mishra
Rahul mishra

Reputation: 82

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%'

Upvotes: 4

Ardalan Shahgholi
Ardalan Shahgholi

Reputation: 12555

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

Upvotes: 4

Padhu
Padhu

Reputation: 107

use sp_helptrigger to find the triggerlist for the associated tables

Upvotes: 7

Saurabh
Saurabh

Reputation: 375

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

Upvotes: 26

dilipkumar katre
dilipkumar katre

Reputation: 166

Much simple query below

select (select [name] from  sys.tables where [object_id] = tr.parent_id ) as TableName ,*  from sys.triggers tr

Upvotes: 0

Buzz
Buzz

Reputation: 6320

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

Upvotes: 62

Manish Prajapati
Manish Prajapati

Reputation: 585

Go through

Need to list all triggers in SQL Server database with table name and table'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

  1. Expand a server group, and then expand a server.

  2. Expand Databases, expand the database in which the table containing the trigger belongs, and then click Tables.

  3. In the details pane, right-click the table on which the trigger exists, point to All Tasks, and then click Manage Triggers.

  4. In Name, select the name of the trigger.

  5. 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.

  6. To check the syntax of the trigger, click Check Syntax.

Upvotes: 3

AnandPhadke
AnandPhadke

Reputation: 13486

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.

Upvotes: 7

Related Questions