Tough Trigger

[nilopher]

Hi
I'm not able to correct the following trigger. I'm facing some errors when executing this trigger. The following is description for following trigger.

cust_trigger_remote - Trigger Name
i declared two variables as lpx and exp
i'm passing the value of first two characters of cust_no to exp and i'm asigning the some value to lpx as if condition.

cust is a local database table.
audit_cust is a remote database table.
remote_database is a database link which is working fine.
When inserting or updating, cust_no will change as lpx and concotinating with rest of cust_no.

The follosing is trigger code.

create or replace trigger cust_trigger_remote
after insert or update on cust
for each row
declare
lpx :new.cust_no%rowtype;
exp :new.cust_no%rowtype;
begin
exp := substr(:new.cust_no,1,2);
if exp = 'MA' then
lpx := 'NS'
elseif exp = 'PM' then
lpx := 'PN'
elseif exp = 'AB' then
lpx := 'SP'
elseif exp = 'BA' then
lpx := 'PS'
elseif exp = 'CD' then
lpx := 'GH'
else exp = 'DC' then
lpx := 'HG'
end if;
if inserting then
insert into audit_cust@remote_database values(lpx || substr(:new.cust_no,3,length(:new.cust_no)),
:new.name,:new.address,:new.balance);
else
update audit_cust@remote_database ac set ac.cust_no=lpx || substr(:new.cust_no,3,length(:new.cust_no)), ac.name=:new.name,ac.address=:new.address,ac.balance=:new.balance
where ac.cust_no=lpx || substr(:new.cust_no,3,length(:new.cust_no));
end if;
end;


After i executing this code following errors is cominig.

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/8 PLS-00103: Encountered the symbol "" when expecting one of the
following:
constant exception
table LONG_ double ref
char time timestamp interval date binary national character
nchar

3/8 PLS-00103: Encountered the symbol "" when expecting one of the
following:
:= . ( @ % ;
The symbol ":=" was substituted for "" to continue.

8/6 PLS-00103: Encountered the symbol "ELSEIF" when expecting one of
the following:
. ( * @ % & = - + ; < / > at in mod not rem
<> or != or ~= >= <= <> and or like
between is null is not || is dangling

Please any body can help me. I'm struggling with this trigger since two days.

[ales]

Hi!
1. Delete the ":new." in the declare part of trigger.
2. Add semicolons to the end of each row with lpx:= ... inside the if-elseif-end if command.

Hope that's all,
Ales

[jmodic]

Removing of ":new." in declaration section will not be enough. As I understand, CUSTNO is not the table, it is the column of the CUST table. So you must use %TYPE, not %ROWTYPE. The declaration should be:

declare
lpx cust.cust_no%type;
exp cust.cust_no%type;
begin
.....

Also if this trigger will execute very frequently, you should change cunstructs like this one:

substr(:new.cust_no,3,length(:new.cust_no))

into this:

substr(:new.cust_no,3)

The third parameter in SUBSTR() defaults to the length of the first parameter, so tere is no need to call additional LENGTH() function.