{[['
']]}
']]}
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?
SELECT
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?
SELECT
sys.tables.name AS Table_Name,
sys.objects.name AS Trigger_name,
sys.sql_modules.definition,
sys.objects.create_date,
sys.objects.modify_date
FROM
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
Post a Comment