Hello
I write sample about triggers and i have question. is my solution
correct and exists better solution?
Regards
Pavel Stehule
DROP SCHEMA safecache CASCADE;
CREATE SCHEMA safecache;
CREATE TABLE safecache.source_tbl(category int, int_value int);
CREATE TABLE safecache.cache(category int, sum_val int);
CREATE OR REPLACE FUNCTION safecache.source_tbl_trg_fce()
RETURNS trigger AS
$$
BEGIN IF TG_OP = 'INSERT' THEN -- row cannot exists in cache -- complication -- I would to finish these transaction
withoutconflict IF NOT EXISTS(SELECT category FROM safecache.cache WHERE
category= NEW.category) THEN LOCK TABLE safecache.cache IN SHARE ROW EXCLUSIVE MODE; -- I have to repeat test
IF NOT EXISTS(SELECT category FROM safecache.cache WHERE category =
NEW.category)THEN INSERT INTO safecache.cache VALUES(NEW.category, NEW.int_value); END IF; ELSE
-- simple UPDATE safecache.cache SET sum_val = sum_val + NEW.int_value WHERE category = NEW.category;
END IF; ELSEIF TG_OP = 'UPDATE' THEN -- if category is without change simple IF NEW.category = OLD.category THEN
UPDATE safecache.cache SET sum_val = sum_val + (NEW.int_value - OLD.int_value) WHERE category =
OLD.category; ELSE -- old category has to exists UPDATE safecache.cache SET sum_val = sum_val -
OLD.int_value WHERE category = OLD.category; -- new category is maybe problem IF NOT EXISTS(SELECT
category FROM safecache.cache WHERE category = NEW.category) THEN LOCK
TABLEsafecache.cache IN SHARE ROW EXCLUSIVE MODE; -- I have to repeat test IF NOT EXISTS(SELECT category
FROM safecache.cache WHERE category = NEW.category) THEN INSERT INTO
safecache.cache VALUES(NEW.category, NEW.int_value); END IF; ELSE -- simple, new category
exists UPDATE safecache.cache SET sum_val = sum_val + OLD.int_value WHERE category =
NEW.category; END IF; END IF; ELSE -- DELETE -- value have to exist in cache, simple UPDATE safecache.cache
SET sum_val = sum_val - OLD.int_value WHERE category = OLD.category; END IF; RETURN NEW;
END
$$ LANGUAGE plpgsql;
CREATE TRIGGER actualise_cache AFTER INSERT OR UPDATE OR DELETE ON safecache.source_tbl FOR EACH ROW EXECUTE
PROCEDUREsafecache.source_tbl_trg_fce();
