MS SQL Azure – TSQL to identify all Triggers in a database / Drop Trigger TSQL syntax

I had been told that it was better not to use Triggers..

Nonetheless I had to learn the hard way.

So fast forward a period and I’m wanting to remove triggers from a database and I first need to find where I had put them in the first instance.

SELECT 
     sysobjects.name AS trigger_name 
    ,USER_NAME(sysobjects.uid) AS trigger_owner 
    ,s.name AS table_schema 
    ,OBJECT_NAME(parent_obj) AS table_name 
    ,OBJECTPROPERTY( id, 'ExecIsUpdateTrigger') AS isupdate 
    ,OBJECTPROPERTY( id, 'ExecIsDeleteTrigger') AS isdelete 
    ,OBJECTPROPERTY( id, 'ExecIsInsertTrigger') AS isinsert 
    ,OBJECTPROPERTY( id, 'ExecIsAfterTrigger') AS isafter 
    ,OBJECTPROPERTY( id, 'ExecIsInsteadOfTrigger') AS isinsteadof 
    ,OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') AS [disabled] 
FROM sysobjects 
/*
INNER JOIN sysusers 
    ON sysobjects.uid = sysusers.uid 
*/  
INNER JOIN sys.tables t 
    ON sysobjects.parent_obj = t.object_id 

INNER JOIN sys.schemas s 
    ON t.schema_id = s.schema_id 
WHERE sysobjects.type = 'TR'

And running this against your database you should see something like this.

And how you drop the triggers

IF OBJECT_ID ('sampledelete2chargehistory', 'TR') IS NOT NULL  
   DROP TRIGGER sampledelete2chargehistory;

Thanks to Stack Overflow – I still get some answers from it over AI

Stackoverflow