Re: Fwd: Trigger on VIEW not firing

[Massimo Costantini <massimo.costantini@xxxxxxxxx>]

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@xxxxxxxxx> wrote:

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

>
> 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