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

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.

CodePudding user response：

There's nothing wrong with the trigger, as far as compilation is concerned (apart from the fact that you "forgot" to END it).

SQL> CREATE OR REPLACE TRIGGER TR_USERS
  2    BEFORE INSERT ON USERS
  3    FOR EACH ROW
  4  BEGIN
  5    SELECT lpad(ltrim(rtrim(to_char(myseq.nextval))), 10, '0')
  6    INTO :new.user_id
  7    FROM dual;
  8  END;
  9  /

Trigger created.

SQL> INSERT INTO USERS (FIRST_NAME) VALUES ('Little');

1 row created.

SQL> SELECT * FROM users;

   USER_ID FIRST_NAME      LAST_NAE   CITY       COUNTRY    PASSW EMAIL_ID
---------- --------------- ---------- ---------- ---------- ----- --------------------
         1 Little

SQL>

However, if USER_ID is NUMBER, you're overcomplicated trigger code because whatever you do with those functions, you'll - at the end - get just a number. As you can see from my example, USER_ID = 1.

If it were VARCHAR2, then

SQL> TRUNCATE TABLE users;

Table truncated.

SQL> ALTER TABLE USERS MODIFY user_id VARCHAR2(10);

Table altered.

SQL> INSERT INTO USERS (FIRST_NAME) VALUES ('Foot');

1 row created.

SQL> SELECT * FROM users;

USER_ID    FIRST_NAME      LAST_NAE   CITY       COUNTRY    PASSW EMAIL_ID
---------- --------------- ---------- ---------- ---------- ----- --------------------
0000000002 Foot

SQL>

See the difference?

Trigger could've been simpler (but not much simpler; you're on 10g, after all) as there's nothing to trim:

SQL> CREATE OR REPLACE TRIGGER TR_USERS
  2    BEFORE INSERT ON USERS
  3    FOR EACH ROW
  4  BEGIN
  5    SELECT lpad(to_char(myseq.nextval), 10, '0')
  6    INTO :new.user_id
  7    FROM dual;
  8  END;
  9  /

Trigger created.

SQL> INSERT INTO USERS (FIRST_NAME) VALUES ('Krishna');

1 row created.

SQL> SELECT * FROM users;

USER_ID    FIRST_NAME      LAST_NAE   CITY       COUNTRY    PASSW EMAIL_ID
---------- --------------- ---------- ---------- ---------- ----- --------------------
0000000002 Foot
0000000003 Krishna

SQL>

CodePudding user response：

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.

CodePudding user response：

Apart from the missing end keyword, you don't need all the character formatting or the 'select from dual'. I would just use:

create or replace trigger tr_users
    before insert on users
    for each row
begin
    :new.user_id := myseq.nextval; 
end;

By the way, your sequence can also be written more simply as:

create sequence myseq;

Also, there is no need to code in uppercase. It's a bad habit from the 1970s.