5

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
6
  • Can you prove to us that it works on insert? Please edit your question to include a similar example and result. Aug 13, 2014 at 10:16
  • Added an example for INSERT.
    – kiradotee
    Aug 13, 2014 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? Aug 13, 2014 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, 2014 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, 2014 at 11:57

1 Answer 1

6

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;
1
  • 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, 2014 at 14:50

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.