Document Actions

How to check the status for a specific trigger in SQL Server

Sometimes it's necessary to check if the status of a specific trigger is enabled or disabled to know if some actions will be or not be performed. This task common lead the developers (and even DBAs) to check this using graphical tools like Enterprise Manager and SQL Server Management Studio. It's an option of course, but there is a way to discover it using TSQL commands.

-- First Let's Create The Table

create table SomeTable (id int)
go

-- And the trigger

create trigger Trg on SomeTable
for insert
as
begin
 raiserror('The trigger is active because an exception was raised',16,1)
 raiserror('The trigger has rolled back the insert action',16,1)
 rollback
end

-- If We try to perform an insert, we can verify that the trigger is active

insert into SomeTable (Id) Values (1)

-- We can se that is active because the record was not in SomeTable

select * from SomeTable

-- Now Let's disable the triggers

alter table SomeTable disable trigger trg

-- We can check that the triggers is disable because the insert action not fails

insert into SomeTable (Id) Values (1)
select * from SomeTable

-- There's no column to check the status of triggers

-- The status columns in sysobjects doesn't mean the status of the triggers since it cover other objects
select * from sysobjects where id = object_id('trg')
select * from sys.objects where object_id = object_id('trg')
select * from sys.triggers where object_id = object_id('trg')

-- We can check the status using objectproperty funcion

select objectproperty(object_id('trg'),'ExecIsTriggerDisabled')

-- If We enable the trigger, the status changes too

alter table SomeTable enable trigger trg
select objectproperty(object_id('trg'),'ExecIsTriggerDisabled')

DBA to DBA

Personal tools