CODEWITHSUNDEEP
sql-serversql-server-2012
NP007
NP007

Reputation: 688

Need to get list of triggers which fired on Delete operation

In my database having number of triggers. From that I need to get list of triggers which fired on Delete operation. So anyone can provide me such script to get list of triggers which executed on delete operation.

Thanks in Advance.

Upvotes: 0

Views: 173

Answers (1)

S3S
S3S

Reputation: 25132

In my database having no of triggers.

If your database doesn't have any triggers, then no trigger would have fired when something was deleted. If you noticed child records were deleted when a parent record was deleted, then you could have ON DELETE CASCADE set for your FOREIGN KEY. You can read about that here.

If you want to list all triggers in your database (even though you said you don't have any), then this answer is a good way to do it.

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'

Upvotes: 3

Related Questions