View Triggers in SQL Server:
To list triggers in SQL server database, you can use following simple query:
select * from sys.triggers
Alternatively, use the system stored procedure sp_helptrigger as well, to list triggers associated with the table:
Exec sp_helptrigger 'dbo.OrderHeader'
The server returns the list of triggers associated with the specified table and displays the type of trigger found in the isupdate, isdelete, isinsert, isafter, and isintead of columns.
You can obtain the code for a trigger using the system stored procedure sp_helptext:
Exec sp_helptext 'dbo.trOrderStatus_U'
Deleting DML Triggers
A trigger can be deleted, as can all other database objects, using the appropriate Drop statement:
if exists (select * from sys.objects
where object_id = object_id(N'[dbo].[trEquipment_IU]')
and type in (N'TA', N’TR'))
DROP TRIGGER [dbo].[trEquipment_IU]
Deleting DDL Triggers
The statement for dropping DDL triggers contains a reference to the scope (level) of the trigger:
if exists (select * from master.sys.server_triggers
where name = N'trdTestCreateLogin2' and parent_class=100)
DROP TRIGGER [trdTestCreateLogin2] ON ALL SERVER
GO
if exists (select * from sys.triggers
where name = N'trdAuditTableChanges' and parent_class=0) DROP TRIGGER [trdAuditTableChanges] ON DATABASE
8f9e7d44-c2e7-4619-ac8b-16cdc08fa09d|0|.0