0

i want to modify my current table which is currently generating id's using a trigger from 1 using sequence_name.nextval to something like DON-100 or REC-124 etc.I have seen some examples doing this using functions which modify the values after the trigger performs it's job which looks like overhead how can i modify my current trigger to produce these custom id's and is not too complex.

CREATE TABLE Atendant (
   ID NUMBER(10) PRIMARY KEY,
   NAME  VARCHAR2(25 CHAR) NOT NULL
);

CREATE SEQUENCE  Atendant_SEQ  NOCACHE;

CREATE or replace TRIGGER  Atendant_BI 
  BEFORE INSERT ON  Atendant 
  FOR EACH ROW
DECLARE
BEGIN
  IF :NEW.ID  IS NULL THEN
    :new.id := "ATN"Atendant_SEQ.nextval;
  END IF;
END;
  • 1
    I take it you want ID to be a VARCHAR then? – Philᵀᴹ Apr 25 '17 at 9:40
  • You have been provided with an answer, but you really shouldn't do this. Keep it as a sequence generated number. You seem to want to build intelligence into the key, but I would discourage this. – unleashed Apr 25 '17 at 13:42
1

You're nearly there! A few small changes needed.

Table definition:

CREATE TABLE Atendant (
   ID VARCHAR(20) PRIMARY KEY,
   NAME  VARCHAR2(25 CHAR) NOT NULL
);

Trigger:

CREATE or replace TRIGGER  Atendant_BI 
  BEFORE INSERT ON  Atendant 
  FOR EACH ROW
DECLARE
BEGIN
  IF :NEW.ID  IS NULL THEN
    :new.id := 'ATN'||Atendant_SEQ.nextval;
  END IF;
END;
/

Test:

SQL> insert into Atendant (NAME) values ('phil');

1 row created.

SQL> select * from Atendant;

ID     NAME
------ ----------
ATN1   phil

SQL>

So all you were really missing was the data type and doing the concatenation correctly.

  • LOL,that escalated quickly :P – Mic Micksy Apr 25 '17 at 14:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.