Re: 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 <barwick(at)gmail(dot)com>wrote:

> 2013/7/30 Massimo Costantini <massimo(dot)costantini(at)gmail(dot)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
>