debugsqlMinor
PostgreSQL: Trigger for updating view doesn't work
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.
Then in another place I execute it:
The result:
On INSERT:
Returns:
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: afterOn 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: afterSolution
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:
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.