Postgres Pro
Downloads
Home   >   mailing lists

Re: Fwd: Trigger on VIEW not firing - Mailing list pgsql-general

From Beena Emerson
Subject Re: Fwd: Trigger on VIEW not firing
Date
Msg-id CAOG9ApGOsziKO0ncBYUY5h2443D=MgbqJV4Z1ZsEX+2+NskhUA@mail.gmail.com
Whole thread Raw
In response to Re: Fwd: Trigger on VIEW not firing  (Massimo Costantini <massimo.costantini@gmail.com>)
Responses Re: Fwd: Trigger on VIEW not firing
List pgsql-general
Tree view
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

pgsql-general by date:

Previous
From: Beena Emerson
Date:
Subject: Re: Fwd: Trigger on VIEW not firing
Next
From: Massimo Costantini
Date:
Subject: Re: Fwd: Trigger on VIEW not firing