APEX

Hi there,

A trigger to set the primary key before inserting a record into a table does not appear to be firing when submitting a page from within Application Builder.

The error message I get is:

ORA-00001: unique constraint (DVDMAN.FILMS_PK) violated

Details are as follows:

Table and trigger was created in schema DVDMAN using Object Browser as:

CREATE TABLE "FILMS"
( "FILM_ID" NUMBER(6,0) NOT NULL ENABLE,
"FILM_NAME" VARCHAR2(60) NOT NULL ENABLE,
CONSTRAINT "FILMS_PK" PRIMARY KEY ("FILM_ID") ENABLE,
CONSTRAINT "FILMS_UK1" UNIQUE ("FILM_NAME") ENABLE
)
/

CREATE OR REPLACE TRIGGER "BI_FILMS"
before insert on "FILMS"
for each row
begin
if :NEW."FILM_ID" is null then
select "FILMS_SEQ".nextval into :NEW."FILM_ID" from dual;
end if;
end;

/
ALTER TRIGGER "BI_FILMS" ENABLE
/

Note: I really should drop the FILMS_UK constraint, but this is not the constraint being violated. It is the FILMS_PK primary key constraint.


The sequence is as follows:

CREATE SEQUENCE "FILMS_SEQ" MINVALUE 1 MAXVALUE 999999 INCREMENT BY 1 START WITH 76 NOCACHE NOORDER NOCYCLE
/


The bit of code that tries to insert the record when the page is submitted is defined in Application Builder/Page Processing/Processes section as a PL/SQL anonymous block. The code in the Source box is as follows:

DECLARE
n_film_id films.film_id%TYPE := 8;
BEGIN
-- SELECT "FILMS_SEQ".nextval INTO n_film_id FROM DUAL;
INSERT INTO films VALUES (n_film_id, :p1_film_name);
END;

Uncommenting the SELECT statement allows the INSERT statement to work, but why is the trigger not firing?

If I try the following code in SQL Workshop/ SQL Commands the record is inserted which means the trigger has fired in this case!

DECLARE
n_film_id films.film_id%TYPE;
p1_film_name films.film_name%TYPE := 'Test film name C';
BEGIN
INSERT INTO films VALUES (n_film_id, p1_film_name);
END;

I am using APEX 3.2 with 10g Express Edition Release 10.2.

Regards.

John.