CODEWITHSUNDEEP
sql-servertriggers
Fraz Sundal
Fraz Sundal

Reputation: 10448

Unable to find where triggers are stored in sql server 2008

I want to delete and modify previously created triggers but i cant find them anywhere in database. Where they exist and how to edit or delele them

Upvotes: 46

Views: 68151

Answers (5)

šljaker
šljaker

Reputation: 7374

You can find Triggers under Table node:

enter image description here

Upvotes: 101

SS64
SS64

Reputation: 363

To expand a little on the previous answers, in all the recent versions of SQL Server you can right click on a trigger and choose: Script Trigger as… ALTER To… "New Query Editor Window"

This will open an SQL script with the details of the trigger, if you read the code you will notice that it includes the ALTER syntax: ALTER TRIGGER [dbo].triggername ...

This means you can edit the SQL and press Execute to alter the trigger - this will overwrite the previous definition.

If the triggers have been built using automated tools, you may find duplicate code in the trigger definition which you will want to remove.

It is worth trying to Execute the script first before trying to edit anything, that will tell you if the trigger definition is valid. If a table or column has been renamed, things can get out of sync.

Similarly to Delete/Drop a trigger completely select: Script Trigger as… DROP To… "New Query Editor Window" and then execute it.

Upvotes: 2

sqlglenn
sqlglenn

Reputation: 101

Here is a better way: 

select a.[name] as trgname, b.[name] as [tbname] 
from sys.triggers a join sys.tables b on a.parent_id = b.object_id

Just be sure to run it against the database where you think the trigger is located.

Upvotes: 10

Oded
Oded

Reputation: 499352

Under the Tables node in SSMS (SQL Server Management Studio), for each table there is a Triggers node.

You can manage your triggers from there.

Upvotes: 13

marc_s
marc_s

Reputation: 755351

You can also find the triggers by querying the management views in SQL Server Management Studio:

SELECT
    OBJECT_NAME(object_id) 'Table name', *
FROM 
    sys.triggers

That gives you a list of all triggers and what table they're defined on for your current database. You can then go on to either disable or drop them.

Upvotes: 5

Related Questions