Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer

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);
share|improve this answer
1  
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. –  Bart Friederichs Aug 20 '13 at 14:58
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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