What are you looking for ?
Home » » Triggers



Trigger Tricks

A trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server. DML triggers execute when a user tries to modify data through a data manipulation language (DML) event. DML events are INSERT, UPDATE, or DELETE statements on a table or view.

But how list all triggers in SQL Server database?

triggers_name.name AS trigger_name,
USER_NAME(triggers_name.uid) AS trigger_owner,
sys.schemas.name AS table_schema,
OBJECT_NAME(triggers_name.parent_obj) AS table_name,
OBJECTPROPERTY(triggers_name.id, 'ExecIsUpdateTrigger') AS isupdate,
OBJECTPROPERTY(triggers_name.id, 'ExecIsDeleteTrigger') AS isdelete,
OBJECTPROPERTY(triggers_name.id, 'ExecIsInsertTrigger') AS isinsert,
OBJECTPROPERTY(triggers_name.id, 'ExecIsAfterTrigger') AS isafter,
OBJECTPROPERTY(triggers_name.id, 'ExecIsInsteadOfTrigger') AS isinsteadof,
OBJECTPROPERTY(triggers_name.id, 'ExecIsTriggerDisabled') AS disabled
FROM            sys.sysobjects AS triggers_name INNER JOIN
                sys.sysusers ON triggers_name.uid = sys.sysusers.uid INNER JOIN
                sys.tables ON triggers_name.parent_obj = sys.tables.object_id INNER JOIN
                sys.schemas ON sys.tables.schema_id = sys.schemas.schema_id
WHERE        (triggers_name.type = 'TR')
Order By  trigger_name

How to Generate Scripts For All Triggers in Database?

sys.tables.name AS Table_Name,
sys.objects.name AS Trigger_name,
sys.sql_modules INNER JOIN
sys.objects ON sys.objects.object_id = sys.sql_modules.object_id INNER JOIN
sys.tables ON sys.objects.parent_object_id = sys.tables.object_id
WHERE (sys.objects.type = 'TR')
ORDER BY Trigger_name
Share this article :

Post a Comment

Flag Counter

Social Profile

Copyright x 2011. By Wael Medhat - All Rights Reserved