Search code examples
sqldatabaseoracle-databasetriggersauto-increment

How to create TRIGGER column id auto increment with string in oracle database?

Result that I want in column id: S-1, S-2, S-3

CREATE OR REPLACE TRIGGER auto_id
BEFORE INSERT ON login 
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW

BEGIN
    :NEW.id := :NEW.id || to_char('"S-"',to_char(seq_log.nextval));
END login;

But when I insert data its error ORA-06502: PL/SQL: numeric or value error: character to number conversion error ORA-06512: at "WKSP_WORKSPACE0089.AUTO_ID", line 3 ORA-04088: error during execution of trigger 'WKSP_WORKSPACE0089.AUTO_ID' ORA-06512: at "SYS.DBMS_SQL", line 1721

Solution

  • Should be

    :NEW.id := :NEW.id || 'S-' || to_char(seq_log.nextval);