CODEWITHSUNDEEP
plsqltriggers
dukenukem6487
dukenukem6487

Reputation: 61

PLSQL - Creation of Trigger throwing errors

I have a table that I need to generate a trigger for. Below is the code I have for it:

CREATE OR REPLACE EDITIONABLE TRIGGER "MYUSER"."REGISTRATION_TRG" BEFORE
  INSERT ON REGISTRATIONS FOR EACH ROW BEGIN <<COLUMN_SEQUENCES>> BEGIN IF INSERTING
  AND :NEW.REGISTRATIONID                              IS NULL THEN
  SELECT REGISTRATIONS_SEQ.NEXTVAL
  INTO :NEW.REGISTRATIONID
  FROM SYS.DUAL;
END IF;
END COLUMN_SEQUENCES;
END;

When I run this I get the following errors:

Error starting at line 0 in command:
CREATE OR REPLACE EDITIONABLE TRIGGER "MYUSER"."REGISTRATIONS_TRG" BEFORE
  INSERT ON REGISTRATIONS FOR EACH ROW BEGIN <<COLUMN_SEQUENCES>> BEGIN IF INSERTING
  AND :NEW.REGISTRATIONID                              IS NULL THEN
  SELECT REGISTRATIONS_SEQ.NEXTVAL
  INTO :NEW.REGISTRATIONID
  FROM SYS.DUAL
Error report:
SQL Command: CREATE OR REPLACE EDITIONABLE
Failed: Warning: execution completed with warning

Error starting at line 7 in command:
END IF
Error report:
Unknown Command

Error starting at line 8 in command:
END COLUMN_SEQUENCES
Error report:
Unknown Command

Error starting at line 9 in command:
END
Error report:
Unknown Command

What am I doing wrong?

Thanks!

Upvotes: 0

Views: 1733

Answers (1)

William Robertson
William Robertson

Reputation: 16001

Firstly, it works for me in SQL*Plus and in PL/SQL Developer:

create table registrations(registrationid integer);

create sequence registrations_seq;

CREATE OR REPLACE EDITIONABLE TRIGGER "REGISTRATION_TRG" BEFORE
  INSERT ON REGISTRATIONS FOR EACH ROW BEGIN <<COLUMN_SEQUENCES>> BEGIN IF INSERTING
  AND :NEW.REGISTRATIONID                              IS NULL THEN
  SELECT REGISTRATIONS_SEQ.NEXTVAL
  INTO :NEW.REGISTRATIONID
  FROM SYS.DUAL;
END IF;
END COLUMN_SEQUENCES;
END;
/

(I removed "MYUSER" because I don't have a user account with that name.)

However, you can simplify this quite a bit. To begin with, it's worth laying out code neatly in any language, so the first step would be:

create or replace editionable trigger registration_trg
    before insert on registrations
    for each row
begin
    <<column_sequences>>
    begin
        if inserting and :new.registrationid is null then
            select registrations_seq.nextval into :new.registrationid
            from sys.dual;
        end if;
    end column_sequences;
end;

Then,

  1. You don't need a named block if it's the only block.
  2. It's an insert trigger, so you don't need to test for if inserting.
  3. Allowing both generated and user-entered values in the same column is a recipe for disaster, but if you must then you can define that as a when condition in the trigger spec.
  4. The PL/SQL language has an assignment operator := so you don't need a query to assign values to variables.

So why not simply:

create or replace trigger registration_trg
    before insert on registrations
    for each row
    when (new.registrationid is null)
begin
    :new.registrationid := registrations_seq.nextval;
end;

Even more simply, from Oracle 12.1 onwards you don't even need a trigger:

drop table registrations;

create table registrations (id integer generated always as identity);

insert into registrations values (default);

select * from registrations;

        ID
----------
         1

Upvotes: 1

Related Questions