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:
> > 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).