Building an app for blocking a demo calendar for particular product setup.
I wanted to create a custom reference ID ( CURRENT_MONTH-CURRENT_YEAR-SEQUENCE like SEPT-2019-003) which will be the Primary Key (Column BOOKING_REF) for the table (table name DEMO_BOOKING).
Table SQL:
CREATE TABLE "DEMO_BOOKING"
(
"BOOKING_REF" VARCHAR2(255) NOT NULL ENABLE,
"REGION" VARCHAR2(100),
"NAME_OF_CUSTOMER" VARCHAR2(500),
"FUSION_ID" VARCHAR2(255),
"PRODUCT_LIST" VARCHAR2(500),
"RAISED_BY" VARCHAR2(500),
"REMARKS" VARCHAR2(2000),
"START_DATETIME" TIMESTAMP (6),
"END_DATETIME" TIMESTAMP (6),
"PRODUCT_LOB" VARCHAR2(10),
"BOOKING_CATEGORY" VARCHAR2(255),
CONSTRAINT "DEMO_BOOKING_CON" PRIMARY KEY ("BOOKING_REF")
USING INDEX ENABLE
)
/Also created a trigger:
CREATE OR REPLACE EDITIONABLE TRIGGER "DEMO_BOOKING_T1"
BEFORE
insert on "DEMO_BOOKING"
for each row
WHEN (NEW.BOOKING_REF IS NULL) begin
:NEW.BOOKING_REF : = EXTRACT(YEAR FROM SYSDATE)||'-'||EXTRACT(MONTH FROM SYSDATE)||'-'||SEQ_BOOKING_REF.NEXTVAL;
end;
I am facing two challenges:1. An issue in Trigger "DEMO_BOOKING_T1"
APEX Showing Error as:
Line Position Text
2 18 PLS-00103: Encountered the symbol " " when expecting one of the following: := . ( @ % ; indicator
2. How to map this trigger "DEMO_BOOKING_T1" to "BOOKING_REF" Field in the APEX Form so that it auto-generate reference number at the time of submission of the form.
1. Look
very carefully at the syntax of the assignment to :NEW.BOOKING_REF.
2. There's nothing for you do to in APEX. The trigger fires every time you insert a row where NEW.BOOKING_REF is null. From any application. That's how triggers work.
Assuming you're on 12c or higher, I'd prefer to use a column default instead of a trigger though:
CREATE TABLE "DEMO_BOOKING" (
"BOOKING_REF" VARCHAR2(255) default
EXTRACT(YEAR FROM SYSDATE)||'-'||
EXTRACT(MONTH FROM SYSDATE)||'-'||
SEQ_BOOKING_REF.NEXTVAL
NOT NULL ENABLE,
"REGION" VARCHAR2(100),
"NAME_OF_CUSTOMER" VARCHAR2(500),
"FUSION_ID" VARCHAR2(255),
"PRODUCT_LIST" VARCHAR2(500),
"RAISED_BY" VARCHAR2(500),
"REMARKS" VARCHAR2(2000),
"START_DATETIME" TIMESTAMP (6),
"END_DATETIME" TIMESTAMP (6),
"PRODUCT_LOB" VARCHAR2(10),
"BOOKING_CATEGORY" VARCHAR2(255),
CONSTRAINT "DEMO_BOOKING_CON" PRIMARY KEY ("BOOKING_REF")
USING INDEX ENABLE
)