1

I am building a database application that uses functions and triggers. One trigger I have limits the possible data that can be put in on INSERT to make sure a record goes through a defined set of states. However, there is a function that inserts a record in that table that fires that trigger (it starts in a different state).

My question now, is it OK to disable that trigger using ALTER TABLE in that function to get this working, or should I rethink my trigger design? Another work around would be to insert it in the "start" state and immediately UPDATE the record to make a state-step.

1 Answer 1

2

You can disable the trigger in the database function, but any actions on the table with the trigger that occur outside of that transaction (functions create implicit transactions) will be blocked until the function completes.

Simple test case:

-- In session 1
drop table if exists test;
create table test ( x int, y text );

create or replace function test_trg_f() returns trigger as $func$
begin
    perform null;
    return NEW;
end;
$func$ language plpgsql;

CREATE TRIGGER test_trg BEFORE INSERT ON public.test FOR EACH ROW EXECUTE PROCEDURE public.test_trg_f();

-- in session 2
DO $$
begin
    alter table test disable trigger test_trg;
    perform pg_sleep(10);
    alter table test enable trigger test_trg;
end;
$$;

-- In session 1, will be blocked until session 2 completes
INSERT INTO test (x,y) VALUES (1,1);
1
  • 2
    I tried it, and found out that it doesn't work, as I cannot even disable the trigger. In the end, it is a design mistake and I should fix the design, not work around it. Aug 20, 2013 at 14:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.