From: | Beena Emerson <memissemerson(at)gmail(dot)com> |
---|---|
To: | Massimo Costantini <massimo(dot)costantini(at)gmail(dot)com> |
Cc: | Ian Lawrence Barwick <barwick(at)gmail(dot)com>, PG-General Mailing List <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Fwd: Trigger on VIEW not firing |
Date: | 2013-07-30 13:30:41 |
Message-ID: | CAOG9ApGOsziKO0ncBYUY5h2443D=MgbqJV4Z1ZsEX+2+NskhUA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgeu-general pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Massimo Costantini | 2013-07-30 13:54:53 | Re: Fwd: Trigger on VIEW not firing |
Previous Message | Beena Emerson | 2013-07-30 13:11:06 | Re: Fwd: Trigger on VIEW not firing |
From | Date | Subject | |
---|---|---|---|
Next Message | Massimo Costantini | 2013-07-30 13:54:53 | Re: Fwd: Trigger on VIEW not firing |
Previous Message | Tom Jenkinson | 2013-07-30 13:25:31 | Re: [GENERAL] Incorrect response code after XA recovery |