Where does a Server trigger save in SQL Server?

Question

A couple of days ago , I was practicing and I wrote some triggers like these :

create trigger trg_preventionDrop
on all server 
for drop_database
as
print 'Can not Drop Database'
rollback tran

go 

create trigger trg_preventDeleteTable
on database
for drop_table
as 
print 'you can not delete any table'
rollback tran

But the problem is I don't know where it has saved and How can I delete or edit those.

Thank you

Martin Smith · Accepted Answer · 2010-10-01T21:58:42.777

11

Server Trigger

You can see them here

select * from sys.server_triggers

To drop use this syntax

drop trigger trg_preventionDrop on all server

In Management Studio they are under the "Server Objects" -> "Triggers" node

Database Trigger

You can see them here

select * from yourdb.sys.triggers

To drop use this syntax

drop trigger trg_preventDeleteTable on database

In Management Studio they are under the "Databases" -> "yourdb" -> "Programmability" -> "Database Triggers" node

edited Oct 01 '10 at 21:58

answered Oct 01 '10 at 21:52

Martin Smith

419,657
83
708
800

The body of normal triggers are stored in sys.sql_modules. Where are the bodies of server triggers stored? – Andrew Savinykh Jul 24 '13 at 06:46
@zespri `sys.server_sql_modules` – Martin Smith Jul 24 '13 at 07:04
@MartinSmith, yep, thanks, found it about ten seconds later after typing the question. – Andrew Savinykh Jul 24 '13 at 08:39

Amit Sinha · Answer 2 · 2012-10-30T10:58:57.760

3

trigger on a particular table resides in "DataBase" -> "YourDb" -> "YourTable" -> "Trigger" folder in Management Studio

also one can find the trigger on a particular table by execution the following sql:
EXEC sp_helptrigger yourtablename

edited Oct 30 '12 at 10:58

answered Oct 30 '12 at 05:03

Amit Sinha

516
7
20

Where does a Server trigger save in SQL Server?

2 Answers2

Linked