How do i assign a custom id using a trigger using sequence on a table

Question

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;

Answer 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.