Professional Web Applications Themes

disable trigger from transaction - PostgreSQL / PGSQL

hello, I am interested in disabling a trigger from a transaction. I am not want to disable the trigger globally but only for the current transaction. Can I do it somehow ? thanks, Razvan Radu ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? [url]http://www.postgresql.org/docs/faq[/url]...

  1. #1

    Default disable trigger from transaction


    hello,

    I am interested in disabling a trigger from a transaction.
    I am not want to disable the trigger globally but only for the current
    transaction.

    Can I do it somehow ?


    thanks,
    Razvan Radu


    ---------------------------(end of broadcast)---------------------------
    TIP 5: Have you checked our extensive FAQ?

    [url]http://www.postgresql.org/docs/faq[/url]

    Postgres General Guest

  2. #2

    Default Re: disable trigger from transaction

    Razvan,

    I don't believe there is a way of doing this from by way of some postgreSQL
    command. We accomplish this by creating a table called "override". It is
    defined as:
    recid | integer | not null default
    nextval('public.override_recid_seq'::text)
    trig_name | character varying | not null
    pid | integer | not null
    batch | character varying | not null
    Indexes:
    "override_pkey" primary key, btree (recid)
    "override_pid_key" unique, btree (pid, trig_name)
    "override_pid_pkey1" btree (pid, batch)

    We use this table to accomplish what you are talking about. We insert into the
    table the trigger name, pid, and some made up string into batch. We use batch
    so we can provide different levels of override, but you may not need that.
    For the triggers we are interested in overriding, we code them to check for
    the existance of a record in override that matches the trigger name and the
    pid, and possibly, a batch name. If we find an override record, we simply
    return.

    Here is an example:
    SELECT INTO ovrRec * FROM override WHERE
    pid = pg_backend_pid () AND trig_name = name;
    IF FOUND THEN
    IF dbg THEN
    RAISE NOTICE ''%: Overriding'', name;
    END IF;
    RETURN true; -- outa here
    END IF;
    RETURN false;

    Actually, we put the above code into a function and call the function from
    triggers that we may need to override from some other place.

    Maybe some of the others have a better way. Hope this helps.
    On Monday 24 January 2005 06:02 am, Postgres General saith:
    > hello,
    >
    > I am interested in disabling a trigger from a transaction.
    > I am not want to disable the trigger globally but only for the current
    > transaction.
    >
    > Can I do it somehow ?
    >
    >
    > thanks,
    > Razvan Radu
    >
    >
    > ---------------------------(end of broadcast)---------------------------
    > TIP 5: Have you checked our extensive FAQ?
    >
    > [url]http://www.postgresql.org/docs/faq[/url]
    __
    Work: 1-336-372-6812
    Cell: 1-336-363-4719
    email: [email]terryesc1.com[/email]

    ---------------------------(end of broadcast)---------------------------
    TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to [email]majordomopostgresql.org[/email])

    Terry Lee Tucker Guest

  3. #3

    Default Re: disable trigger from transaction

    Would it work to just do a DROP TRIGGER at the begining of the
    transaction and a CREATE TRIGGER at the end?

    Regards,
    Jeff Davis

    On Mon, 2005-01-24 at 06:50 -0500, Terry Lee Tucker wrote:
    > Razvan,
    >
    > I don't believe there is a way of doing this from by way of some postgreSQL
    > command. We accomplish this by creating a table called "override". It is
    > defined as:
    > recid | integer | not null default
    > nextval('public.override_recid_seq'::text)
    > trig_name | character varying | not null
    > pid | integer | not null
    > batch | character varying | not null
    > Indexes:
    > "override_pkey" primary key, btree (recid)
    > "override_pid_key" unique, btree (pid, trig_name)
    > "override_pid_pkey1" btree (pid, batch)
    >
    > We use this table to accomplish what you are talking about. We insert into the
    > table the trigger name, pid, and some made up string into batch. We use batch
    > so we can provide different levels of override, but you may not need that.
    > For the triggers we are interested in overriding, we code them to check for
    > the existance of a record in override that matches the trigger name and the
    > pid, and possibly, a batch name. If we find an override record, we simply
    > return.
    >
    > Here is an example:
    > SELECT INTO ovrRec * FROM override WHERE
    > pid = pg_backend_pid () AND trig_name = name;
    > IF FOUND THEN
    > IF dbg THEN
    > RAISE NOTICE ''%: Overriding'', name;
    > END IF;
    > RETURN true; -- outa here
    > END IF;
    > RETURN false;
    >
    > Actually, we put the above code into a function and call the function from
    > triggers that we may need to override from some other place.
    >
    > Maybe some of the others have a better way. Hope this helps.
    > On Monday 24 January 2005 06:02 am, Postgres General saith:
    > > hello,
    > >
    > > I am interested in disabling a trigger from a transaction.
    > > I am not want to disable the trigger globally but only for the current
    > > transaction.
    > >
    > > Can I do it somehow ?
    > >
    > >
    > > thanks,
    > > Razvan Radu
    > >
    > >
    > > ---------------------------(end of broadcast)---------------------------
    > > TIP 5: Have you checked our extensive FAQ?
    > >
    > > [url]http://www.postgresql.org/docs/faq[/url]
    > __
    > Work: 1-336-372-6812
    > Cell: 1-336-363-4719
    > email: [email]terryesc1.com[/email]
    >
    > ---------------------------(end of broadcast)---------------------------
    > TIP 2: you can get off all lists at once with the unregister command
    > (send "unregister YourEmailAddressHere" to [email]majordomopostgresql.org[/email])

    ---------------------------(end of broadcast)---------------------------
    TIP 5: Have you checked our extensive FAQ?

    [url]http://www.postgresql.org/docs/faq[/url]

    Jeff Davis Guest

  4. #4

    Default Re: disable trigger from transaction

    I don't know if droping a trigger inside a transaction will work. Besides
    that, we want the trigger to do its work in all other cirstances. With a
    hundred connections on the database, I don't know what kind of issues that
    would cause if the trigger were there, and suddenly, not there. We figured
    this was a safe approach.

    On Monday 24 January 2005 01:27 pm, Jeff Davis saith:
    > Would it work to just do a DROP TRIGGER at the begining of the
    > transaction and a CREATE TRIGGER at the end?
    >
    > Regards,
    > Jeff Davis
    >
    > On Mon, 2005-01-24 at 06:50 -0500, Terry Lee Tucker wrote:
    > > Razvan,
    > >
    > > I don't believe there is a way of doing this from by way of some
    > > postgreSQL command. We accomplish this by creating a table called
    > > "override". It is defined as:
    > > recid | integer | not null default
    > > nextval('public.override_recid_seq'::text)
    > > trig_name | character varying | not null
    > > pid | integer | not null
    > > batch | character varying | not null
    > > Indexes:
    > > "override_pkey" primary key, btree (recid)
    > > "override_pid_key" unique, btree (pid, trig_name)
    > > "override_pid_pkey1" btree (pid, batch)
    > >
    > > We use this table to accomplish what you are talking about. We insert
    > > into the table the trigger name, pid, and some made up string into batch.
    > > We use batch so we can provide different levels of override, but you may
    > > not need that. For the triggers we are interested in overriding, we code
    > > them to check for the existance of a record in override that matches the
    > > trigger name and the pid, and possibly, a batch name. If we find an
    > > override record, we simply return.
    > >
    > > Here is an example:
    > > SELECT INTO ovrRec * FROM override WHERE
    > > pid = pg_backend_pid () AND trig_name = name;
    > > IF FOUND THEN
    > > IF dbg THEN
    > > RAISE NOTICE ''%: Overriding'', name;
    > > END IF;
    > > RETURN true; -- outa here
    > > END IF;
    > > RETURN false;
    > >
    > > Actually, we put the above code into a function and call the function
    > > from triggers that we may need to override from some other place.
    > >
    > > Maybe some of the others have a better way. Hope this helps.
    > >
    > > On Monday 24 January 2005 06:02 am, Postgres General saith:
    > > > hello,
    > > >
    > > > I am interested in disabling a trigger from a transaction.
    > > > I am not want to disable the trigger globally but only for the current
    > > > transaction.
    > > >
    > > > Can I do it somehow ?
    > > >
    > > >
    > > > thanks,
    > > > Razvan Radu
    > > >
    > > >
    > > > ---------------------------(end of
    > > > broadcast)--------------------------- TIP 5: Have you checked our
    > > > extensive FAQ?
    > > >
    > > > [url]http://www.postgresql.org/docs/faq[/url]
    > >
    > > __
    > > Work: 1-336-372-6812
    > > Cell: 1-336-363-4719
    > > email: [email]terryesc1.com[/email]
    > >
    > > ---------------------------(end of broadcast)---------------------------
    > > TIP 2: you can get off all lists at once with the unregister command
    > > (send "unregister YourEmailAddressHere" to [email]majordomopostgresql.org[/email])
    >
    > ---------------------------(end of broadcast)---------------------------
    > TIP 5: Have you checked our extensive FAQ?
    >
    > [url]http://www.postgresql.org/docs/faq[/url]
    --
    Work: 1-336-372-6812
    Cell: 1-336-363-4719
    email: [email]terryesc1.com[/email]

    ---------------------------(end of broadcast)---------------------------
    TIP 6: Have you searched our list archives?

    [url]http://archives.postgresql.org[/url]

    Terry Lee Tucker Guest

  5. #5

    Default Re: disable trigger from transaction

    It got me curious enough that I tested it, and apparently droping a
    trigger locks the table. Any actions on that table must wait until the
    transaction that drops the trigger finishes.

    So, technically my system works, but requires a rather nasty lock while
    the transaction (the one that doesn't want the trigger to execute)
    finishes.

    Yours doesn't require any special locking, so it seems yours would be
    the preferred solution.

    Regards,
    Jeff Davis

    On Mon, 2005-01-24 at 13:45 -0500, Terry Lee Tucker wrote:
    > I don't know if droping a trigger inside a transaction will work. Besides
    > that, we want the trigger to do its work in all other cirstances. With a
    > hundred connections on the database, I don't know what kind of issues that
    > would cause if the trigger were there, and suddenly, not there. We figured
    > this was a safe approach.
    >
    > On Monday 24 January 2005 01:27 pm, Jeff Davis saith:
    > > Would it work to just do a DROP TRIGGER at the begining of the
    > > transaction and a CREATE TRIGGER at the end?
    > >
    > > Regards,
    > > Jeff Davis
    > >
    > > On Mon, 2005-01-24 at 06:50 -0500, Terry Lee Tucker wrote:
    > > > Razvan,
    > > >
    > > > I don't believe there is a way of doing this from by way of some
    > > > postgreSQL command. We accomplish this by creating a table called
    > > > "override". It is defined as:
    > > > recid | integer | not null default
    > > > nextval('public.override_recid_seq'::text)
    > > > trig_name | character varying | not null
    > > > pid | integer | not null
    > > > batch | character varying | not null
    > > > Indexes:
    > > > "override_pkey" primary key, btree (recid)
    > > > "override_pid_key" unique, btree (pid, trig_name)
    > > > "override_pid_pkey1" btree (pid, batch)
    > > >
    > > > We use this table to accomplish what you are talking about. We insert
    > > > into the table the trigger name, pid, and some made up string into batch.
    > > > We use batch so we can provide different levels of override, but you may
    > > > not need that. For the triggers we are interested in overriding, we code
    > > > them to check for the existance of a record in override that matches the
    > > > trigger name and the pid, and possibly, a batch name. If we find an
    > > > override record, we simply return.
    > > >
    > > > Here is an example:
    > > > SELECT INTO ovrRec * FROM override WHERE
    > > > pid = pg_backend_pid () AND trig_name = name;
    > > > IF FOUND THEN
    > > > IF dbg THEN
    > > > RAISE NOTICE ''%: Overriding'', name;
    > > > END IF;
    > > > RETURN true; -- outa here
    > > > END IF;
    > > > RETURN false;
    > > >
    > > > Actually, we put the above code into a function and call the function
    > > > from triggers that we may need to override from some other place.
    > > >
    > > > Maybe some of the others have a better way. Hope this helps.
    > > >
    > > > On Monday 24 January 2005 06:02 am, Postgres General saith:
    > > > > hello,
    > > > >
    > > > > I am interested in disabling a trigger from a transaction.
    > > > > I am not want to disable the trigger globally but only for the current
    > > > > transaction.
    > > > >
    > > > > Can I do it somehow ?
    > > > >
    > > > >
    > > > > thanks,
    > > > > Razvan Radu
    > > > >
    > > > >
    > > > > ---------------------------(end of
    > > > > broadcast)--------------------------- TIP 5: Have you checked our
    > > > > extensive FAQ?
    > > > >
    > > > > [url]http://www.postgresql.org/docs/faq[/url]
    > > >
    > > > __
    > > > Work: 1-336-372-6812
    > > > Cell: 1-336-363-4719
    > > > email: [email]terryesc1.com[/email]
    > > >
    > > > ---------------------------(end of broadcast)---------------------------
    > > > TIP 2: you can get off all lists at once with the unregister command
    > > > (send "unregister YourEmailAddressHere" to [email]majordomopostgresql.org[/email])
    > >
    > > ---------------------------(end of broadcast)---------------------------
    > > TIP 5: Have you checked our extensive FAQ?
    > >
    > > [url]http://www.postgresql.org/docs/faq[/url]
    >

    ---------------------------(end of broadcast)---------------------------
    TIP 3: if posting/reading through Usenet, please send an appropriate
    subscribe-nomail command to [email]majordomopostgresql.org[/email] so that your
    message can get through to the mailing list cleanly

    Jeff Davis Guest

  6. #6

    Default Re: disable trigger from transaction

    I'm glad your curiosity got the best of you ;o)

    I was planning to test it out, but didn't have the time to do it. I too, was
    very curious as to what the ramifications of dropping the trigger would be in
    that scenario. Now, we know :o)

    On Monday 24 January 2005 11:07 pm, Jeff Davis saith:
    > It got me curious enough that I tested it, and apparently droping a
    > trigger locks the table. Any actions on that table must wait until the
    > transaction that drops the trigger finishes.
    >
    > So, technically my system works, but requires a rather nasty lock while
    > the transaction (the one that doesn't want the trigger to execute)
    > finishes.
    >
    > Yours doesn't require any special locking, so it seems yours would be
    > the preferred solution.
    >
    > Regards,
    > Jeff Davis
    >
    > On Mon, 2005-01-24 at 13:45 -0500, Terry Lee Tucker wrote:
    > > I don't know if droping a trigger inside a transaction will work. Besides
    > > that, we want the trigger to do its work in all other cirstances. With
    > > a hundred connections on the database, I don't know what kind of issues
    > > that would cause if the trigger were there, and suddenly, not there. We
    > > figured this was a safe approach.
    > >
    Work: 1-336-372-6812
    Cell: 1-336-363-4719
    email: [email]terryesc1.com[/email]

    ---------------------------(end of broadcast)---------------------------
    TIP 1: subscribe and unsubscribe commands go to [email]majordomopostgresql.org[/email]

    Terry Lee Tucker 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. How to lock or disable a trigger
    By sid tow in forum PostgreSQL / PGSQL
    Replies: 1
    Last Post: January 25th, 07:18 AM
  3. Replies: 5
    Last Post: September 18th, 09:15 AM
  4. Transaction id and transaction isolation
    By Alex in forum Informix
    Replies: 3
    Last Post: July 23rd, 09:33 PM
  5. advanced: Transaction-logging Trigger
    By vsiat in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: March 18th, 11:34 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