46

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

Fraz Sundal
  • 10,288
  • 22
  • 81
  • 132
  • you can also use this link : [where does a server trigger save in sql server][1] [1]: http://stackoverflow.com/questions/3843256/where-does-a-server-trigger-save-in-sql-server – esmail ebrahimi Feb 23 '14 at 19:38

5 Answers5

97

You can find Triggers under Table node:

enter image description here

šljaker
  • 7,294
  • 14
  • 46
  • 80
13

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.

Oded
  • 489,969
  • 99
  • 883
  • 1,009
9

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.

Jon Crowell
  • 21,695
  • 14
  • 89
  • 110
sqlglenn
  • 91
  • 1
  • 1
5

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
2

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.

SS64
  • 334
  • 5
  • 12
  • 26
  • And of course you shouls save this script and put it into source control. All database objects definitions should be in source control like any other kind of code. – HLGEM Jul 02 '13 at 17:46