consistency of Trigger Procedure (before row trigger) Postgresql
- by elgcom
Using Postgresql.
I try to use TRIGGER procedure to make some consistency check on INSERT.
The question is ......
whether "BEFORE INSERT FOR EACH ROW" can make sure each row to insert "checked" and "inserted" one after another? do I need extra lock on table to survive from concurrent insert?
check for new row1 - insert row1 - check for new row2 - insert row2
--
--
-- unexpired product name is unique.
CREATE TABLE product (
"name" VARCHAR(100) NOT NULL,
"expired" BOOLEAN NOT NULL
);
CREATE OR REPLACE FUNCTION check_consistency() RETURNS TRIGGER AS $$
BEGIN
IF EXISTS (SELECT * FROM product WHERE name=NEW.name AND expired='false') THEN
RAISE EXCEPTION 'duplicated!!!';
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trigger_check_consistency
BEFORE INSERT ON product
FOR EACH ROW EXECUTE PROCEDURE check_consistency();
--
INSERT INTO product VALUES("prod1", true);
INSERT INTO product VALUES("prod1", false);
INSERT INTO product VALUES("prod1", false); // exception!
this is OK
name | expired
==============
p1 | true
p1 | true
p1 | false
This is not OK
name | expired
==============
p1 | true
p1 | false
p1 | false
or maybe I should ask,
how can I use Trigger to implement "Primary" or "Unique" constraint-like SQL.
