I want to create a trigger, so SEQNUM gets a next value everytime an insert is done in the table.
This is the script to create the trigger:
CREATE OR REPLACE TRIGGER JITEM_TG
BEFORE INSERT
ON MYSCHEMA.JITEM_A
FOR EACH ROW
BEGIN
:NEW.SEQNUM := MYSCHEMA.JITEM_SEQ.NEXTVAL ;
END;
The script gets executed, a trigger is created, but there is a compile error:
[Warning] ORA-24344: success with compilation error
2/19 PLS-00201: identifier 'MYSCHEMA.JITEM_SEQ' must be declared
2/4 PL/SQL: Statement ignored
(1: 0): Warning: compiled but with compilation errors
Now, in the table all_triggers I can find the trigger.
But the trigger does not work. When an insert is done following error is given:
[Error] Execution (10: 19): ORA-04098: trigger 'SYSADM.JITEM_TG' is invalid and failed re-validation
The sequence is working, so for example when I execute following statment it is given me the next value:
select MYSCHEMA.JITEM_SEQ.nextval from dual;
Searched for similar issues, but did not find any answers that appeared applicable.
Or found the answer, where the answer is exactly as the script I am trying to execute. (mostly a colon or semicolon missing, but I think that’s fine in my script)
Did you, by any chance, create that sequence and enclosed its name into double quotes?
Have a look at the following demo:
Sample table:
SQL> CREATE TABLE jitem_a
2 (
3 seqnum NUMBER,
4 name VARCHAR2 (20)
5 );
Table created.
Sequence; note lower letter case and double quotes:
SQL> CREATE SEQUENCE "jitem_seq";
Sequence created.
Trigger:
SQL> CREATE OR REPLACE TRIGGER JITEM_TG
2 BEFORE INSERT
3 ON JITEM_A
4 FOR EACH ROW
5 BEGIN
6 :NEW.SEQNUM := JITEM_SEQ.NEXTVAL;
7 END;
8 /
Warning: Trigger created with compilation errors.
Errors? What errors?! Your errors!
SQL> show err
Errors for TRIGGER JITEM_TG:
LINE/COL ERROR
-------- -----------------------------------------------------------------
2/4 PL/SQL: Statement ignored
2/19 PLS-00201: identifier 'JITEM_SEQ.NEXTVAL' must be declared
If you used mixed letter case and double quotes, you must use the same when referencing such an object:
SQL> CREATE OR REPLACE TRIGGER JITEM_TG
2 BEFORE INSERT
3 ON JITEM_A
4 FOR EACH ROW
5 BEGIN
6 :NEW.SEQNUM := "jitem_seq".NEXTVAL; --> here
7 END;
8 /
Trigger created.
OK, now it is created. Does it work? Yes, it does:
SQL> INSERT INTO jitem_a (name) VALUES ('Littlefoot');
1 row created.
SQL> SELECT * FROM jitem_a;
SEQNUM NAME
---------- --------------------
1 Littlefoot
SQL>
If that’s really the case, I’d suggest you to remove double quotes entirely when working with Oracle.
1