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 <[email protected]>wrote:
> 2013/7/30 Massimo Costantini <[email protected]>:
> >
> > 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
>
