Trigger Sql Statement
-
i have a select statement for Oracle that is:
Select Trigger_Name, table_name , table_owner, status from user_triggers where triggering_event = 'DELETE'
I am not very fimliar with SQL server system tables and was wondering if anyone could help in getting a select statement that would do the samething that the Oracle on above does
Thanks
Tricia Colwell
-
Select name from dbo.SysObjects where XType = 'TR' and OBJECTPROPERTY(id, 'ExecIsDeleteTrigger') = 1 order by name
-
Thanks that helps but i still need to know how to get the table name that the trigger is to and the owner of that table and the status of the trigger.
Tricia
-
select tr.[name] as Trigger_Name,
p.[name] as Table_Name,
user_name(p.uid) as Table_Owner,
case
when OBJECTPROPERTY(tr.id, 'ExecIsTriggerDisabled') = 1 THEN 'Disabled'
when OBJECTPROPERTY(tr.id, 'ExecIsTriggerDisabled') = 0 THEN 'Enabled'
else 'UNKNOWN'
end as Trigger_Status
from
sysobjects p join sysobjects tr on tr.parent_obj = p.id
where
p.xtype = 'U'
and tr.xtype = 'TR'
and OBJECTPROPERTY(tr.id, 'ExecIsDeleteTrigger') = 1
order by
tr.[name], p.[name]
* Noel -
Thanks for your help
Tricia
-
You are welcome
* Noel -
Another question with triggers:
In oracle we use this statement to update a trigger:
"Alter trigger triggerName operation
where operation is either enable or disable.
How do i do this in SQL Server??
Tricia
-
ALTER TABLE TableName {ENABLE | DISABLE} TriggerName
However you cannot tell it to stop firing on delete but continue firing on inserts and updates.
-
However you cannot tell it to stop firing on delete but continue firing on inserts and updates
If you need that functionality just keep each trigger code separated
(one for insert, one for delete and one for update)
* Noel
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply