consistency of Trigger Procedure (before row trigger) Postgresql
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?

  check for new row1 -> insert row1 -> check for new row2 -> insert row2



    --
    --
    
    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='true') 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", true); // exception!
consistency of Trigger Procedure (before row trigger) Postgresql
===

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?

  check for new row1 -> insert row1 -> check for new row2 -> insert row2



    --
    --
    
    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='true') 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", true); // exception!