Oracle 10g Error: Warning: Trigger created with compilation errors

Question

I have created database where I want autoincrement the primary key. I tried to trigger it but getting the above error here is my description of table:

SQL> desc users
Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 USER_ID                                   NOT NULL NUMBER(8)
 FIRST_NAME                                NOT NULL VARCHAR2(50)
 LAST_NAME                                 NOT NULL VARCHAR2(50)
 CITY                                               VARCHAR2(20)
 COUNTRY                                            VARCHAR2(20)
 PASSWORD                                  NOT NULL VARCHAR2(16)
 EMAIL_ID                                  NOT NULL VARCHAR2(50)

when I am trying to trigger it getting the error:

 CREATE SEQUENCE SYSTEM.MYSEQ
 2  START WITH 1
 3  MAXVALUE 99999999
 4  MINVALUE 1
 5  NOCYCLE
 6  CACHE 20
 7  NOORDER;

CREATE OR REPLACE TRIGGER TR_USERS BEFORE INSERT ON USERS FOR EACH ROW
2  BEGIN SELECT LPAD(LTRIM(RTRIM(TO_CHAR(MYSEQ.NEXTVAL))),10,'0') INTO :NEW.USER_ID FROM DUAL;
3  /

please help me to solve this error.

Answer 1

You are getting the error, because you are missing the trigger's END:

CREATE OR REPLACE TRIGGER TR_USERS
  BEFORE INSERT ON USERS
  FOR EACH ROW
BEGIN
  SELECT LPAD(LTRIM(RTRIM(TO_CHAR(MYSEQ.NEXTVAL))),10,'0')
    INTO :NEW.USER_ID 
  FROM DUAL;
END; -- <=== this one
/

The trigger doesn't seem to make much sense, by the way. LPAD(LTRIM(RTRIM(TO_CHAR(MYSEQ.NEXTVAL))),10,'0') is just an obfuscated TO_CHAR(MYSEQ.NEXTVAL, 'FM0000000000'), but then, why create a string with leading zeros, when USERS.USER_ID is numeric??? You turn 123 into '0000000123' only to store it as 123.