CODEWITHSUNDEEP
oracle-databasesqlplus
kenpeter
kenpeter

Reputation: 8274

PLS-00103: Create trigger for sequence number oracle

I have this script. Basically increase one of the primary key in a table.

CREATE OR REPLACE FORCE TRIGGER SISD_OWNER.TRG_SN_MAP_UNIT_RELA_SEQ
BEFORE INSERT ON SISD_OWNER.ADV_SN_MAP_UNIT_RELA
FOR EACH ROW
DECLARE
  m_next_seq_num NUMBER := 0;
BEGIN
  IF :new.SEQUENCE_NUMBER is null THEN
    select
        ADV_SN_MAP_UNIT_RELA_SEQ.NextVal
    into m_next_seq_num
    from dual;
    :new.SEQUENCE_NUMBER := m_next_seq_num;
  END IF;
END;

When run on sqlplus (cmd), I got:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/28     PLS-00103: Encountered the symbol "end-of-file" when expecting
     one of the following:
     * & = - + ; < / > at in is mod remainder not rem
     <an exponent (**)> <> or != or ~= >= <= <> and or like like2
     like4 likec between || multiset member submultiset

Not sure what exactly is the error?

Upvotes: 0

Views: 117

Answers (2)

Dmitry Demin
Dmitry Demin

Reputation: 2113

If you have a database version 11g or higher. Then use new features that simplify the code and increase productivity.

CREATE OR REPLACE TRIGGER SISD_OWNER.TRG_SN_MAP_UNIT_RELA_SEQ
BEFORE INSERT ON SISD_OWNER.ADV_SN_MAP_UNIT_RELA
FOR EACH ROW
DECLARE

BEGIN
  IF :new.SEQUENCE_NUMBER is null THEN
    :new.SEQUENCE_NUMBER := ADV_SN_MAP_UNIT_RELA_SEQ.NextVal;
  END IF;
END;

Upvotes: 1

Jon Heller
Jon Heller

Reputation: 36817

Remove the keyword FORCE. That option does not exist for triggers. (Although I wish it did - it would be nice sometimes to create a trigger before the table or sequence existed.)

enter image description here

Upvotes: 0

Related Questions