Checking if any trigger is disabled.

  • Hi

    I wanted to put a simple query in a proc and set a daily job that will tell me if any trigger is disabled.

    Following query can help me with this.

    SELECT name, objectproperty(object_id(name),'execistriggerdisabled')

    FROM  sysobjects

    WHERE type = 'TR'

    AND objectproperty(object_id(name),'execistriggerdisabled') = 1

    But I wanted to keep procedure with above query in a central  database (dbadmin or whatever) and wanted to run it from there for various databases on that server by somehow passing the database name.

    I think I can run a query like  "....FROM DBNAME.SYSOBJECTS" format but "Objectproperty" doesn't work when I have the procedure in one database and want to run the query for some other database.

    In short, say I have a procedure with above query in some "dbadmin or xyz" database can I run it from there to see if any trigger is disabled in "Northwind or Pubs or ...whatever" database without moving that procedure to every single database on that server.

    Thanks for all the help.

     

     

     

     

  • This will get you going :

    CREATE TABLE dbo.Test (id int not null primary key clustered)

    GO

    CREATE TRIGGER dbo.tr_test_IUD ON dbo.Test

    FOR INSERT, UPDATE, DELETE

    AS

    SET NOCOUNT ON

    ROLLBACK TRANSACTION

    RAISERROR ('This is just a test table', 13, 1)

    SET NOCOUNT OFF

    GO

    ALTER TABLE dbo.Test DISABLE TRIGGER tr_test_IUD

    GO

    EXEC SP_MSForEachDB 'USE ? SELECT ''?'' AS [?], name AS TriggerName, objectproperty(object_id(name),''execistriggerdisabled'') AS TriggerIsDisabled FROM ?.dbo.sysobjects WHERE type = ''TR'' AND objectproperty(object_id(name),''execistriggerdisabled'') = 1'

    GO

    DROP TABLE dbo.Test

    BTW I think you'd be better inserting into a global table, then selecting from it at the end.

  • Thank you so very much for your help RGR I really appreciate it.

    Regards

     

  • HTH.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply