Why is trigger not fired on every single row when

问题:

I defined a BEFORE INSERT trigger for a table and it works as expected for single INSERTstatements, but not for INSERT ... SELECT nor MERGE statements.

These are my database objects (simplified):

CREATE TABLE "COMPANY" (
    "ID" NUMBER NOT NULL,
    "NAME" VARCHAR(100)
);

CREATE TABLE "EMPLOYEE" (
    "ID" NUMBER NOT NULL,
    "COMPANY_ID" NUMBER NOT NULL
);

CREATE UNIQUE INDEX "EMPLOYEE_PK" ON "EMPLOYEE" ("ID");

CREATE SEQUENCE "EMPLOYEE_SEQUENCE";

CREATE TRIGGER "BI_EMPLOYEE" BEFORE INSERT ON "EMPLOYEE" 
    REFERENCING NEW AS newrow FOR EACH ROW BEGIN ATOMIC 
    IF newrow.id IS NULL THEN 
        SET newrow.id = NEXT VALUE FOR employee_sequence;
    END IF;
END;

If single INSERTstatements are executed, everything works as expected, the ÌD is fetched from the sequence. But if I execute something like

INSERT INTO employee (company_id) SELECT id FROM company;

the I get an error:

integrity constraint violation: unique constraint or index violation: "EMPLOYEE_PK"

which could propably mean that it tries to insert the same key from the sequence twice.

I'm using the latests version 2.3.2 of HSQLDB.

回答1:

Because triggers are set based, not row based.

See details here