0

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.

NP007
  • 659
  • 8
  • 21

1 Answers1

3

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'
S3S
  • 24,809
  • 5
  • 26
  • 45
  • 1
    This answer helps to me. I just add where condition in above query to get expected output. In my database having number of triggers. – NP007 Jan 17 '19 at 08:22