Professional Web Applications Themes

Disable Triggers On Update - Microsoft SQL / MS SQL Server

You can use ALTER TABLE ...DISABLE TRIGGER.. to do this. For exact syntax refer to SQL Server Books Online. -- - Anith ( Please reply to newsgroups only )...

  1. #1

    Default Re: Disable Triggers On Update

    You can use ALTER TABLE ...DISABLE TRIGGER.. to do this. For exact syntax
    refer to SQL Server Books Online.

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Sen Guest

  2. #2

    Default Re: Disable Triggers On Update


    "Anith Sen" <anithbizdatasolutions.com> wrote in message
    news:OOArVzXRDHA.2144TK2MSFTNGP11.phx.gbl...
    > You can use ALTER TABLE ...DISABLE TRIGGER.. to do this. For exact syntax
    > refer to SQL Server Books Online.
    >
    Which is the right answer.

    But you can also do this use "set context_info" to pass data into your
    trigger.
    This is only slightly less hastle than writing the data to a table and
    reading it from your trigger, but "set context_info" is the _only_ method
    SQLServer has to write to a table outside of a transactional context. Kind
    of like an autonomous transaction. It's mildly interesting just for that.

    Davdi



    create table foo(i int)
    create table foo2(i int)

    go
    create trigger foo_ins on foo for insert
    as
    declare v varbinary(128)
    select v = context_info FROM master.dbo.sysprocesses WHERE spid = SPID

    if v = cast('no triggers' as varbinary(128))
    return

    insert into foo2(i) select i from inserted


    go


    insert into foo(i) values(1)

    declare v as varbinary(128)
    set v = cast('no triggers' as varbinary(128))
    set context_info v

    insert into foo(i) values(2)


    set v = cast('' as varbinary(128))
    set context_info v

    insert into foo(i) values(3)



    David Browne Guest

  3. #3

    Default Re: Disable Triggers On Update

    The Alter Table solution would work in change scripts, but
    to do this in a stored procedure, I would need to grant
    dbo access to the stored procedure for the user who will
    call it. This is just my reading, but it appears that
    Alter Table requires the user to be either an owner of the
    table or a dbo. Am I reading this correctly? Is there
    another way?

    Thanks -=- Keith Roe
    Keith Roe Guest

Similar Threads

  1. How to disable copy/past (selectable='disable') fromTextArea
    By Dronius in forum Macromedia Flex General Discussion
    Replies: 5
    Last Post: April 10th, 09:48 PM
  2. update in triggers
    By Jamie Deppeler in forum PostgreSQL / PGSQL
    Replies: 5
    Last Post: January 19th, 12:31 PM
  3. How to disable Product Update requests and registration notifications
    By Tom_Casacchia@adobeforums.com in forum Adobe Acrobat Windows
    Replies: 3
    Last Post: June 8th, 07:05 AM
  4. triggers in DB2
    By Piotr in forum IBM DB2
    Replies: 2
    Last Post: August 27th, 03:37 PM
  5. Replies: 6
    Last Post: January 3rd, 10:51 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139