Oracle had introduced an identity column in 12C and it is to generate auto sequence no for a primary key in a table. A table can have only a single identity column and it has
GENERATED [ ALWAYS | BY DEFAULT [ ON NULL ] ] AS IDENTITY [ ( identity_options ) ]
create table emp2 (
id number generated always as identity nocache
constraint emp_id_pk primary key,
name varchar2(255 char)
);
create table emp (
id number generated by default on null as identity
constraint emp_id_pk primary key,
name varchar2(255 char)
);
create table emp (
id number generated always as identity
constraint emp_id_pk primary key,
name varchar2(255 char)
);
CREATE TABLE "EMP" ( "EMPNO" NUMBER(4,0) GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 MAXVALUE 90000 INCREMENT BY 1 START WITH 8000 NOCACHE NOORDER NOCYCLE NOKEEP NOSCALE NOT NULL ENABLE,
"ENAME" VARCHAR2(50),
"JOB" VARCHAR2(50),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(4,0),
CONSTRAINT "EMP_PK" PRIMARY KEY ("EMPNO")
USING INDEX ENABLE
)
Due to identity columns now you don't need a sequence for primary key.
Trigger
create or replace trigger js_party_biu
before insert or update
on js_party
for each row
begin
if inserting then
Select js_party_s.nextval INTO :new.ID from dual;
:new.creation_date := sysdate;
:new.created_by := coalesce(sys_context('APEX$SESSION','APP_USER'),user);
end if;
:new.updation_date := sysdate;
:new.updated_by := coalesce(sys_context('APEX$SESSION','APP_USER'),user);
end js_party_biu;
VIDEO
https://youtu.be/pDkLHO1C9FU
SEQUENCES | Primary Key | ID | Foreign Key
SEQUENCES | Primary Key | ID | Foreign Key
Malik Sikandar Hayat
admin@erpstuff.com
admin@erpstuff.com
Who is online
Users browsing this forum: No registered users and 1 guest