Re: [GENERAL] Fwd: Trigger on VIEW not firing
ok, thank to all On Tue, Jul 30, 2013 at 3:30 PM, Beena Emerson wrote: > 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 > >
Re: [GENERAL] Fwd: Trigger on VIEW not firing
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
Re: [GENERAL] Fwd: Trigger on VIEW not firing
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
Re: [GENERAL] Fwd: Trigger on VIEW not firing
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 wrote: > 2013/7/30 Massimo Costantini : > > > > 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 >
Re: [GENERAL] Fwd: Trigger on VIEW not firing
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.costant...@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
Re: [GENERAL] Fwd: Trigger on VIEW not firing
2013/7/30 Massimo Costantini : > > 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
[GENERAL] Fwd: 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?
