HiveBrain v1.2.0
Get Started
← Back to all entries
debugsqlMinor

PostgreSQL: Trigger for updating view doesn't work

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
postgresqltriggerupdatingviewdoesnforwork

Problem

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

Solution

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;

Code Snippets

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;

Context

StackExchange Database Administrators Q#73875, answer score: 6

Revisions (0)

No revisions yet.