CODEWITHSUNDEEP
sqloracle-databaseplsqloracle-apex
Jevon T
Jevon T

Reputation: 3

Can't compile my trigger in Apex Oracle SQL/PL PLS-00103

create or replace TRIGGER  lening_bri
BEFORE
update on lening
for each row
begin

if :old.terugbetaald = 'N' and :new.terugbetaald = 'Y' then
  :new.datumterugbetaald := sysdate;
end if;

if :old.terugbetaald = 'Y' and :new.terugbetaald = 'N' then
   :new.datumterugbetaald := NULL;
end if;

if :old.terugbetaald = 'N' and :new.terugbetaald = 'Y' then
   :new.datumterugbetaald := sysdate;
end if;

if old.datumterugbetaald := NULL and :new.datumterugbetaald := NOT NULL then
    :new.terugbetaald := 'Y'
end if;

if :old.datumterugbetaald := NOT NULL and :new.datumterugbetaald := NULL then
    :new.terugbetaald := 'N'
end if;

end;

I can't compile it. I get this error every time:

PLS-00103: Encountered the symbol "=" when expecting one of the following: . ( * @ % & = - + < / > at in is mod remainder not rem then <an exponent (**)> <> or != or ~= >= <= <> and or like like2 like4 likec between || multiset member submultiset

For the last two if statements, it has to do the following:

Upvotes: 0

Views: 61

Answers (1)

Alex Poole
Alex Poole

Reputation: 191275

The last two if statements are using the := assignment operator instead of the = equality operator. One of the old references is missing its leading colon too, and you're missing a couple of statement-ending semicolons.

But you can't compare null with equality checks, there are dedicated operators you have to use instead.

So those should be:

if :old.datumterugbetaald IS NULL and :new.datumterugbetaald IS NOT NULL then
    :new.terugbetaald := 'Y';
end if;

if :old.datumterugbetaald IS NOT NULL and :new.datumterugbetaald IS NULL then
    :new.terugbetaald := 'N';
end if;

You also need to consider what that column should be set to if neither if evaluates to true - if the column you're checking was and stays null, or was and stays not not (including if it changes value).

The same is true for your first three checks too; and your first and third checks seem to be the same.

Upvotes: 1

Related Questions