Thread: Make a SQL statement not run trigger
Is it possible to run an insert,update, or delete and have it not launch a trigger like it normally would? For example could I set a value DONOTRUN = True; insert into contacts .... Where the trigger on contacts would call a function that would have an IF statment for that DONOTRUN value? Or is there just a global variable I could set to disable triggers and then reset it? And would that be a per connection variable? Thanks
On 8/21/07, Jon Collette <jon@etelos.com> wrote: > Is it possible to run an insert,update, or delete and have it not launch > a trigger like it normally would? alter table disable trigger ... http://www.postgresql.org/docs/8.2/static/sql-altertable.html
Thanks for the reply. It looks like this will disable the trigger for all connections to the database. So if the chance occured that another connection was using the table at the same time it wouldn't launch the trigger either? am I wrong? I hope so ;) Rodrigo De León wrote: > On 8/21/07, Jon Collette <jon@etelos.com> wrote: > >> Is it possible to run an insert,update, or delete and have it not launch >> a trigger like it normally would? >> > > alter table disable trigger ... > > http://www.postgresql.org/docs/8.2/static/sql-altertable.html > > ---------------------------(end of broadcast)--------------------------- > TIP 7: You can help support the PostgreSQL project by donating at > > http://www.postgresql.org/about/donate >
On 8/21/07, Jon Collette <jon@etelos.com> wrote: > Is it possible to run an insert,update, or delete and have it not launch > a trigger like it normally would? > > For example could I set a value > DONOTRUN = True; > insert into contacts .... The closest thing to a session variable for pgsql is going to likely be a temp table. you could have a temp table for each session that stores such things and the trigger looks in the temp table to see what to do. > Or is there just a global variable I could set to disable triggers and > then reset it? And would that be a per connection variable? Yes, but that would open you up to race conditions. If another session wanted the trigger to fire it would not get it. Using a temp table would allow you to get around the race condition.
Jon Collette wrote on 21.08.2007 23:26: > Is it possible to run an insert,update, or delete and have it not launch > a trigger like it normally would? > > For example could I set a value > DONOTRUN = True; > insert into contacts .... > > Where the trigger on contacts would call a function that would have an > IF statment for that DONOTRUN value? > > Or is there just a global variable I could set to disable triggers and > then reset it? And would that be a per connection variable? What we have done once, was to include a column in the table for this purpose. If a special value for the column was provided during UPDATE or INSERT, the trigger would immediately terminate, not doing any work. Thus the trigger still "fires" every time, but simply won't do nothing. Not very elegant, but worked for our environment. Thomas
Good Idea. However some of my triggers use the DELETE action as well. So I can't use this method. Thomas Kellerer wrote: > Jon Collette wrote on 21.08.2007 23:26: >> Is it possible to run an insert,update, or delete and have it not >> launch a trigger like it normally would? >> >> For example could I set a value >> DONOTRUN = True; >> insert into contacts .... >> >> Where the trigger on contacts would call a function that would have >> an IF statment for that DONOTRUN value? >> >> Or is there just a global variable I could set to disable triggers >> and then reset it? And would that be a per connection variable? > > What we have done once, was to include a column in the table for this > purpose. If a special value for the column was provided during UPDATE > or INSERT, the trigger would immediately terminate, not doing any > work. Thus the trigger still "fires" every time, but simply won't do > nothing. Not very elegant, but worked for our environment. > > Thomas > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org
I think this will work for what I need. I have been messing around with this using select into /select True as donothing into temporary table table_trigger_name; then run statement that I want to be ignored / The trigger would have a select upon the table_trigger_name to determine if it should run or not. I am having issues catching the exception when the table is not found. This is my test function of my trigger /Create or replace function trigger_test() returns boolean as $$ declare donothing boolean; begin donothing := False; select donothing into donothing from table_trigger_name; return boolean; end; $$ LANGUAGE plpgsql; / Which of course will error when there is no table_trigger_name made for that session. I couldn't find an exception in the exceptions list for table not found errors. Scott Marlowe wrote: > On 8/21/07, Jon Collette <jon@etelos.com> wrote: > >> Is it possible to run an insert,update, or delete and have it not launch >> a trigger like it normally would? >> >> For example could I set a value >> DONOTRUN = True; >> insert into contacts .... >> > > The closest thing to a session variable for pgsql is going to likely > be a temp table. you could have a temp table for each session that > stores such things and the trigger looks in the temp table to see what > to do. > > >> Or is there just a global variable I could set to disable triggers and >> then reset it? And would that be a per connection variable? >> > > Yes, but that would open you up to race conditions. If another > session wanted the trigger to fire it would not get it. Using a temp > table would allow you to get around the race condition. > > ---------------------------(end of broadcast)--------------------------- > TIP 7: You can help support the PostgreSQL project by donating at > > http://www.postgresql.org/about/donate >
On 8/21/07, Jon Collette <jon@etelos.com> wrote: > I think this will work for what I need. I have been messing around with > this using select into > > /select True as donothing into temporary table table_trigger_name; > then run statement that I want to be ignored > / > The trigger would have a select upon the table_trigger_name to determine > if it should run or not. I am having issues catching the exception when > the table is not found. > Note that if you use temp tables much, you need to make sure that autovacuum is up and running (or vacuum regularly) to keep the system catalogs from getting bloated. Just FYI.