Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Location
    Argentina
    Posts
    26

    Unanswered: Disabling triggers from stored procedures

    Hi,

    Is it possible to disable a trigger from a Stored Procedure? If it is, how do you do it?


    Thanks,


    Federico

  2. #2
    Join Date
    Aug 2003
    Location
    Kingston, Ontario
    Posts
    106
    ALTER TABLE [MyTable] DISABLE TRIGGER trMyTrigger

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Just curious as to why you would want to do this?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Yes, it is possible to disable the trigger, but keep in mind that you are disabling that trigger for everyone using the table, not just for what the trigger is doing. I don't know of any way to allow a trigger to function for some users/spids and not for others, except to include code within the trigger to make it decide that it shouldn't run under some conditions.

    -PatP

  5. #5
    Join Date
    Mar 2004
    Posts
    45
    If SQL Server 2000 and one can alter the trigger then one can use SET CONTEXT_INFO in SP and then check master..sysprocesses from trigger.

    Might also insert DBCC INPUTBUFFER(@@SPID) results into temporary table in trigger to check for proc name in EventInfo column. Would work in SQL Server 7 and would not require any additional code in SP, but would hurt trigger performance, be kludgy, and require hard-coding proc name(s) in trigger.

Posting Permissions

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