Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2003
    Posts
    233

    Unanswered: use a cursor to disable triggers on a table whose name is in a table

    I have a table that list all triggers on sometable i need to disable. the table would look like

    tblMyTriggers
    tableName trigger
    tbl_A ta1
    tbl_A ta2
    tbl_A ta3
    tbl_b tb1
    tbl_C tc1
    tbl_C tc2


    if would like to do a select from the table tblMyTriggers
    and then for each trigger disable it. I never used a cursor but im pretty sure that is what i would use to accomplish this.

    THNKS!

    ps the code for disabling the trigger:
    DISABLE TRIGGER Person.uAddress ON Person.Address;

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    DECLARE    @s    AS NVARCHAR(4000)
    
    SELECT    @s = COALESCE(@s, '') + REPLACE(REPLACE('DISABLE TRIGGER Person.[trigger] ON myDB.Person.[tableName];', '[trigger]', [trigger]), '[tableName]', tableName)
    FROM    dbo.tblMyTriggers
    
    EXECUTE    (@s)
    Won't work if you have loads of triggers.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Oct 2003
    Posts
    233
    What are loads triggers how can i tell if i have them?

    Quote Originally Posted by pootle flump
    Code:
    DECLARE    @s    AS NVARCHAR(4000)
    
    SELECT    @s = COALESCE(@s, '') + REPLACE(REPLACE('DISABLE TRIGGER Person.[trigger] ON myDB.Person.[tableName];', '[trigger]', [trigger]), '[tableName]', tableName)
    FROM    dbo.tblMyTriggers
    
    EXECUTE    (@s)
    Won't work if you have loads of triggers.

  4. #4
    Join Date
    Oct 2003
    Posts
    233
    I am an sql caveman all i know is simple selects and stored procs. Could you explain your code to me?

    one of the lines created was:
    DISABLE TRIGGER [dbo].[trg_Dtlupdt] ON [dbo].[TblAcct] ;

    this is giving me the error:
    Msg 156, Level 15, State 1, Line 1
    Incorrect syntax near the keyword 'TRIGGER'.


    also, i am trying to check if the triggers are disabled with

    SELECT *
    FROM sys.triggers
    WHERE is_disabled = 1 -- Disabled
    ORDER BY [Name];

    and getting the error msg:
    Msg 208, Level 16, State 1, Line 1
    Invalid object name 'sys.triggers'.

    whats that about LOL
    Last edited by mikezx10; 03-12-09 at 15:10.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    DECLARE    @s    AS NVARCHAR(4000)
    
    SELECT    @s = COALESCE(@s, '') + REPLACE(REPLACE('DISABLE TRIGGER Person.[trigger] ON myDB.Person.[tableName];', '[trigger]', [trigger]), '[tableName]', tableName)
    FROM    dbo.tblMyTriggers
    
    PRINT @s
    Ok - run this. Look at what is printed out. This is the DDL statement that will disable all triggers. I have used a T-SQL extension - it mimics what would happen if you looped through the table with a cursor, concatenating all your DISABLE TRIGGER statements into a single string, but without the hassle of writing loads of code.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by mikezx10
    also, i am trying to check if the triggers are disabled
    What version of SQL Server?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Oct 2003
    Posts
    233
    ooopss my bad 2000! i got every thing to work except the validating that the triggers were disabled:

    SELECT *
    FROM sys.triggers
    WHERE is_disabled = 1 -- Disabled
    ORDER BY [Name]

    still getting the error
    Msg 208, Level 16, State 1, Line 1
    Invalid object name 'sys.triggers'.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    sys.triggers is for 2005. I forget the 2000 syntax. You can get the names of triggers in 2000 using dbo.sysobjects, but I don't think you can get the enabled property. Google, or check BoL.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    You don't have the option of disabling a trigger in MSSQL 2000. You may want to drop the trigger, but you have to save trigger's body to be able to re-create it.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by aflorin27
    You don't have the option of disabling a trigger in MSSQL 2000. You may want to drop the trigger, but you have to save trigger's body to be able to re-create it.
    I had forgotten this.
    OP - This is a very good example of why you should test the very basis your concept before you start thinking about things like cursors, meta data tables etc.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •