3

I'm trying to make the trigger for updating a view work. If I'm doing and INSERT it works! But when I'm trying to do an UPDATE it just doesn't run.

CREATE OR REPLACE FUNCTION test_trigger_function()
RETURNS trigger AS
$$
    BEGIN
        RAISE NOTICE 'hi';
        RETURN NEW;
    END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER test_trigger
INSTEAD OF INSERT OR UPDATE
    ON specific_view
        FOR EACH ROW
            EXECUTE PROCEDURE test_trigger_function();

Then in another place I execute it:

RAISE NOTICE 'before';
UPDATE specific_view SET gender='randomstuff';
RAISE NOTICE 'after';

The result:

psql:test_function.pgsql:134: NOTICE:  before
psql:test_function.pgsql:134: NOTICE:  after

On INSERT:

RAISE NOTICE 'before';
INSERT INTO specific_view (gender)
    VALUES ('randomstuff');
RAISE NOTICE 'after';

Returns:

psql:test_function.pgsql:134: NOTICE:  before
psql:test_function.pgsql:134: NOTICE:  hi
CONTEXT:  SQL statement "INSERT INTO specific_view (gender)
                VALUES ('randomstuff')"
PL/pgSQL function ... blahblahblah ... line 261 at SQL statement
psql:test_function.pgsql:134: NOTICE:  after
  • Can you prove to us that it works on insert? Please edit your question to include a similar example and result. – Colin 't Hart Aug 13 '14 at 10:16
  • Added an example for INSERT. – kiradotee Aug 13 '14 at 10:29
  • as far as I know the view taking values from table(s) so how can you update the view if the table(s) values are not updated, shouldn't you make the trigger on update table instead? – Ahmad Abuhasna Aug 13 '14 at 11:41
  • For me to update the table values I need the trigger to work. :) The "RAISE NOTICE 'hi';" is me just testing if the trigger is actually being executed and as you can see it's not. – kiradotee Aug 13 '14 at 11:49
  • P.S. I know that if you want to update a simple view (where there's just 1 table in it) then no trigger is needed, however, my view has 10+ tables in it, therefore I need a trigger for it. If only I knew why it isn't working. – kiradotee Aug 13 '14 at 11:57
3

Because update trigger is invoked once per row being updated, it is not invoked at all if no rows are going to be updated.

See for yourself:

create table test(i int);
create view test_view as select i from test;

CREATE OR REPLACE FUNCTION test_trigger_function()
RETURNS trigger AS
$$
    BEGIN
        RAISE NOTICE 'hi';
        RETURN NEW;
    END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER test_trigger
INSTEAD OF INSERT OR UPDATE
    ON test_view
        FOR EACH ROW
            EXECUTE PROCEDURE test_trigger_function();

insert into test_view(i) values(1);            

SELECT * FROM TEST;
SELECT * FROM test_view;

UPDATE test_view set i=1;
-- trigger did not fire

insert into test(i) values(1);  

UPDATE test_view set i=3;
-- trigger fired

SELECT * FROM test_view;
  • Oh god, yeah I though I had at least 1 row in that view, now I actually did the INSERT first and then UPDATE and it worked. Thanks, silly me. :D – kiradotee Aug 13 '14 at 14:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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