Dear Sir,
Good Evening, Can we restrict data entry after one record is created?
Ex:An end-user has created one record but he/she shouldn't be able to enter a second record into the application/DB
Thanks in Advance
❇️ OrclQA.Com is a question and answer forum for programmers.
❇️ Here anyone can ask questions and anyone can answer to help others.
❇️ It hardly takes a minute to sign up and it is 100% FREE.
Lost your password? Please enter your email address. You will receive a link and will create a new password via email.
OrclQA.Com is a question and answer forum for programmers.
Here anyone can ask questions and anyone can answer to help others.
It hardly takes a minute to sign up and it is 100% FREE.
damirko
Example, compound trigger on table CODES:
--------------------------------------------------------
-- DDL for Sequence CODES_SEQ
--------------------------------------------------------
CREATE SEQUENCE "CODES_SEQ" MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 21 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE GLOBAL ;
--------------------------------------------------------
-- DDL for Table CODES
--------------------------------------------------------
CREATE TABLE "CODES"
( "ID" NUMBER,
"CODE" VARCHAR2(20 BYTE),
"NAME" VARCHAR2(200 BYTE)
) ;
--------------------------------------------------------
-- DDL for Index TABLE1_PK
--------------------------------------------------------
CREATE UNIQUE INDEX "TABLE1_PK" ON "CODES" ("ID")
;
--------------------------------------------------------
-- DDL for Trigger CODES_BI
--------------------------------------------------------
CREATE OR REPLACE EDITIONABLE TRIGGER "CODES_BI"
before insert on "CODES"
for each row
begin
if inserting then
if :NEW."ID" is null then
select CODES_SEQ.nextval into :NEW."ID" from dual;
end if;
end if;
end;
/
ALTER TRIGGER "CODES_BI" ENABLE;
--------------------------------------------------------
-- DDL for Trigger CODES_CMP
--------------------------------------------------------
CREATE OR REPLACE EDITIONABLE TRIGGER "CODES_CMP"
for insert or update on codes
compound trigger
l_cnt number;
after statement is
begin
select count(*) into l_cnt from codes;
if l_cnt > 1 then
raise_application_error( -20001, 'More than 1 row');
end if;
end after statement;
end codes_cmp;
/
ALTER TRIGGER "CODES_CMP" ENABLE;
--------------------------------------------------------
-- Constraints for Table CODES
--------------------------------------------------------
ALTER TABLE "CODES" MODIFY ("ID" NOT NULL ENABLE);
ALTER TABLE "CODES" MODIFY ("CODE" NOT NULL ENABLE);
ALTER TABLE "CODES" MODIFY ("NAME" NOT NULL ENABLE);
ALTER TABLE "CODES" ADD CONSTRAINT "TABLE1_PK" PRIMARY KEY ("ID")
USING INDEX ENABLE;
ALTER TABLE "CODES" ADD CONSTRAINT "CODES_UK1" UNIQUE ("CODE")
USING INDEX ENABLE;
ALTER TABLE "CODES" ADD CONSTRAINT "CODES_UK2" UNIQUE ("NAME")
USING INDEX ENABLE;
Richmond
Dear User,
i believe you can implement unique constraints on a table where the unique identifier will be the :app_user.
Thank you