After insert trigger not work
От | Brilliantov Kirill Vladimirovich |
---|---|
Тема | After insert trigger not work |
Дата | |
Msg-id | 5464A3A1.9080609@byterg.ru обсуждение исходный текст |
Ответы |
Re: After insert trigger not work
|
Список | pgsql-general |
Hello! I use postgre-9.3.5 on windows7 x64. Trigger should update data in table: CREATE TABLE trassa.ram_free_stat ( id serial NOT NULL, device integer NOT NULL, min_value integer NOT NULL, avg_value integer NOT NULL DEFAULT 0, max_value integer NOT NULL, last_update timestamp without time zone NOT NULL DEFAULT now(), CONSTRAINT ram_free_stat_pk PRIMARY KEY (id), CONSTRAINT ram_free_stat_device_fk FOREIGN KEY (device) REFERENCES trassa.devices (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT ram_free_stat_max_fk FOREIGN KEY (max_value) REFERENCES trassa.ram (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT ram_free_stat_min_fk FOREIGN KEY (min_value) REFERENCES trassa.ram (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION ) My trigger: CREATE OR REPLACE FUNCTION trassa.update_ram_free_stat() RETURNS trigger AS $BODY$ DECLARE device_id INTEGER DEFAULT 0; min_id INTEGER DEFAULT 0; avg_val INTEGER DEFAULT 0; max_id INTEGER DEFAULT 0; BEGIN SELECT id INTO device_id FROM trassa.ram_free_stat WHERE device = NEW.device; SELECT free_ram, id INTO min_id FROM trassa.ram WHERE device = NEW.device ORDER BY free_ram LIMIT 1; SELECT free_ram, id INTO max_id FROM trassa.ram WHERE device = NEW.device ORDER BY free_ram DESC LIMIT 1; SELECT CEIL(AVG(free_ram)) INTO avg_val FROM trassa.ram WHERE device = NEW.device; IF device_id > 0 THEN UPDATE trassa.ram_free_stat SET min_value = min_id, avg_value = avg_val, max_value = max_id WHERE id = device_id; ELSE INSERT INTO trassa.ram_free_stat (device, min_value, avg_value, max_value) VALUES(NEW.device, min_id, avg_val, max_id); END IF; RETURN NULL; END; $BODY$ LANGUAGE plpgsql VOLATILE SECURITY DEFINER COST 100; I add this trigger on another table: CREATE TRIGGER update_ram_free_stat_trigger AFTER INSERT ON trassa.ram FOR EACH ROW EXECUTE PROCEDURE trassa.update_ram_free_stat(); All executed without any error, but data in trassa.ram_free_stat not updated. Can you help me solve this problem? Thank you and excuse me for my bad english. -- Best regards, Brilliantov Kirill Vladimirovich
В списке pgsql-general по дате отправления: