Trigger on VIEW not firing

Hi,

I have a problem with Triggers on VIEW:

suppose I have:

CREATE TABLE work (
id integer NOT NULL,
work TEXT,
worktype TEXT
);

CREATE VIEW worksub AS SELECT FROM work WHERE worktype='subordinate';

CREATE OR REPLACE FUNCTION wrk_view() RETURNS TRIGGER AS $wrk_tg$
BEGIN
RAISE NOTICE 'UPDATE VIEW FROM: % OPERATION: %',TG_TABLE_NAME,
TG_OP;
END;
$wrk_tg$ LANGUAGE plpgsql;

CREATE TRIGGER wrk_tg INSTEAD OF INSERT OR DELETE OR UPDATE ON worksub
FOR EACH ROW EXECUTE PROCEDURE wrk_view();

nothing appen when I insert row in work table.

Someone can help me?

2013/7/30 Massimo Costantini <massimo.costantini@gmail.com>:

Hi,

I have a problem with Triggers on VIEW:

suppose I have:

CREATE TABLE work (
id integer NOT NULL,
work TEXT,
worktype TEXT
);

CREATE VIEW worksub AS SELECT FROM work WHERE worktype='subordinate';

CREATE OR REPLACE FUNCTION wrk_view() RETURNS TRIGGER AS $wrk_tg$
BEGIN
RAISE NOTICE 'UPDATE VIEW FROM: % OPERATION: %',TG_TABLE_NAME,
TG_OP;
END;
$wrk_tg$ LANGUAGE plpgsql;

CREATE TRIGGER wrk_tg INSTEAD OF INSERT OR DELETE OR UPDATE ON worksub
FOR EACH ROW EXECUTE PROCEDURE wrk_view();

nothing appen when I insert row in work table.

The trigger is on the view "worksub", not the "work" table.

BTW the trigger function doesn't return anything, which will cause an error.
(Also the view definition is missing column definitions in the SELECT clause).

Regards

Ian Barwick

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

The trigger is on the view and fires when you query the view:

# DELETE FROM worksub;
NOTICE: UPDATE VIEW FROM: worksub OPERATION: DELETE
ERROR: control reached end of trigger procedure without RETURN
CONTEXT: PL/pgSQL function wrk_view()

On Tue, Jul 30, 2013 at 6:10 PM, Massimo Costantini <
massimo.costantini@gmail.com> wrote:

Hi,

I have a problem with Triggers on VIEW:

suppose I have:

CREATE TABLE work (
id integer NOT NULL,
work TEXT,
worktype TEXT
);

CREATE VIEW worksub AS SELECT FROM work WHERE worktype='subordinate';

CREATE OR REPLACE FUNCTION wrk_view() RETURNS TRIGGER AS $wrk_tg$
BEGIN
RAISE NOTICE 'UPDATE VIEW FROM: % OPERATION: %',TG_TABLE_NAME,
TG_OP;
END;
$wrk_tg$ LANGUAGE plpgsql;

CREATE TRIGGER wrk_tg INSTEAD OF INSERT OR DELETE OR UPDATE ON worksub
FOR EACH ROW EXECUTE PROCEDURE wrk_view();

nothing appen when I insert row in work table.

Someone can help me?

--

*
*
Beena Emerson

Thisi is my real situation, can I do this:

CREATE TABLE alarm(
name text,
id integer,
type text,
init timestamp,
fired timestamp,
end timestamp,
user test
);

CREATE TABLE car (
id integer,
type text,
speed double
);

CREATE VIEW speedv AS SELECT * FROM car WHERE speed>100;

CREATE OR REPLACE FUNCTION update_alarm_view() RETURNS TRIGGER AS $alarm_tg$
BEGIN
IF (TG_OP = 'UPDATE') THEN
INSERT INTO alarm VALUES(NEW.type,
0,'SPEED',now(),NULL,NULL,'');
RETURN NEW;
ELSEIF (TG_OP = 'INSERT') THEN
INSERT INTO alarm VALUES(NEW.type,
0,'SPEED',now(),NULL,NULL,'');
RETURN NEW;
END IF;
END;
$alarm_tg$ LANGUAGE plpgsql;

CREATE TRIGGER alarm_tg INSTEAD OF INSERT OR DELETE OR UPDATE ON speedv
FOR EACH ROW EXECUTE PROCEDURE update_alarm_view();

On Tue, Jul 30, 2013 at 2:49 PM, Ian Lawrence Barwick <barwick@gmail.com>wrote:

Massimo,

You need to Insert a record into View to get that trigger fired, since you
have created trigger on View not table.

postgres=# insert into worksub values(1,'test','test');

NOTICE: UPDATE VIEW FROM: worksub OPERATION: INSERT
ERROR: control reached end of trigger procedure without RETURN
CONTEXT: PL/pgSQL function wrk_view()

Hope this helps.

On Tue, Jul 30, 2013 at 5:57 PM, Massimo Costantini <
massimo.costantini@gmail.com> wrote:

Hi,

I have a problem with Triggers on VIEW:

suppose I have:

CREATE TABLE work (
id integer NOT NULL,
work TEXT,
worktype TEXT
);

CREATE VIEW worksub AS SELECT FROM work WHERE worktype='subordinate';

CREATE OR REPLACE FUNCTION wrk_view() RETURNS TRIGGER AS $wrk_tg$
BEGIN
RAISE NOTICE 'UPDATE VIEW FROM: % OPERATION: %',TG_TABLE_NAME,
TG_OP;
END;
$wrk_tg$ LANGUAGE plpgsql;

CREATE TRIGGER wrk_tg INSTEAD OF INSERT OR DELETE OR UPDATE ON worksub
FOR EACH ROW EXECUTE PROCEDURE wrk_view();

nothing appen when I insert row in work table.

Someone can help me?

--
Thanks & Regards,
Ashutosh.Durugkar
Database Admin Oracle & PostgreSQL
Cell- +919960937294

It works fine if you insert into the view:

=# INSERT INTO speedv VALUES (1, 'test', 100);
INSERT 0 1

=# SELECT * FROM alarm;
name | id | type | init | fired | t_end | t_user
------+----+-------+----------------------------+-------+-------+--------
test | 0 | SPEED | 2013-07-30 18:08:01.006979 | | |
(1 row)

=# SELECT * FROM car;
id | type | speed
----+------+-------
(0 rows)

=# SELECT * FROM speedv;
id | type | speed
----+------+-------
(0 rows)

--

*
*
Beena Emerson

Hi again,

IIUC you want to update the alarm table only when the speed limit is above
100. You cannot achieve it by the view and triggers you have written here
because the trigger will be fired even for values < 100

=# INSERT INTO speedv VALUES (1, 'test', 10);
INSERT 0 1

=# SELECT * FROM speedv;
id | type | speed
----+------+-------
(0 rows)

=# SELECT * FROM car;
id | type | speed
----+------+-------
(0 rows)

=# SELECT * FROM alarm;
name | id | type | init | fired | t_end | t_user
------+----+-------+----------------------------+-------+-------+--------
test | 0 | SPEED | 2013-07-30 18:08:01.006979 | | |
test | 0 | SPEED | 2013-07-30 18:20:00.73507 | | |
(2 rows)

If you want to update the alarm table for speed > 100 then use an if else
clause in the trigger function:

CREATE OR REPLACE FUNCTION update_alarm_view() RETURNS TRIGGER AS $alarm_tg$
BEGIN
IF (new.speed > 100) THEN
IF (TG_OP = 'UPDATE') THEN
INSERT INTO alarm VALUES(NEW.type,
0,'SPEED',now(),NULL,NULL,'');
ELSEIF (TG_OP = 'INSERT') THEN
INSERT INTO alarm VALUES(NEW.type,
0,'SPEED',now(),NULL,NULL,'');
END IF;
END IF;
RETURN new;
END;
$alarm_tg$ LANGUAGE plpgsql;

And write the trigger on the car table.

--

Beena Emerson

ok, thank to all

On Tue, Jul 30, 2013 at 3:30 PM, Beena Emerson <memissemerson@gmail.com>wrote: