Oracle SQL Create Trigger to insert SEQ.nextval

Question

I am creating a trigger to add same SEQ.nextval numbers to two fields such as follows:

SITE_NUM  SITE_NUM_COPY  Site_Name
346        346           XYZ
347        347           ABC
348        348           DEF

Whenever a new row is added through an application I want the SITE_NUM and SITE_NUM_COPY will be auto generated through the trigger.

This is what I have put together:

create or replace trigger SITE_TRIGGER
before insert or update on STRATEGY_SITES for each row
begin
if updating then
    if :new.SITE_NUM is null and :new.SITE_NUM_Copy is NULL then
        :new.SITE_NUM := :old.SITE_NUM
        and :old.SITE_NUM := :new.SITE_NUM_COPY;
    end if;
end if;
if inserting then
    :new.SITE_NUM := SITENUM_SEQ.nextval 
    and :new.SITE_NUM_COPY := SITENUM_SEQ.nextval;
end if;
end;

Getting the following error:

Error(7,31): PLS-00103: Encountered the symbol "=" when expecting one of the following:
. ( * @ % & = - + ; < / > at in is mod remainder not rem <> or != or ~= >= <= <> and or like like2 like4 likec between || indicator multiset member submultiset
The symbol "* was inserted before "=" to continue.

Alex Poole · Accepted Answer · 2018-06-22 23:28:22Z

The error is not very helpful but is stemming from you using and to, apparently, seperate two statements inside each if block. The statement terminator/separator is a semicolon.

It looks like you are trying to do this for the first one:

if updating then
    if :new.SITE_NUM is null and :new.SITE_NUM_Copy is NULL then
        :new.SITE_NUM := :old.SITE_NUM;
        :old.SITE_NUM := :new.SITE_NUM_COPY; -- illegal
    end if;
end if;

but you cannot change the :old values; it isn't clear what you expect to happen, ot least as the value assigning must be null. You may just want to remove that line.

And for the second part:

if inserting then
    :new.SITE_NUM := SITENUM_SEQ.nextval;
    :new.SITE_NUM_COPY := SITENUM_SEQ.currval;
end if;

I've changed the second assignment to use currval because you said you wanted the same value for both columns. If you call nextval for both assignments they will get different values simce the sequnce will increment twice.

Collectives™ on Stack Overflow

Oracle SQL Create Trigger to insert SEQ.nextval

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related