Home » SQL & PL/SQL » SQL & PL/SQL » Trigger Error (Oracle 8i,windows XP)
Trigger Error [message #439078] Thu, 14 January 2010 01:54 Go to next message
studydumps
Messages: 2
Registered: January 2010
Location: india
Junior Member
Hi,

I m creating Database Link for linking remote databases.
By Linking this I want to get records from a local table to a remote table whenever this local table updates.
I created one trigger for this which is being executed in the local database which is used to update the remote database table whenever a particular table gets updated or new records is being added

But i am facing a error while the trigger is being compiled

this is the error i am facing


PLS-00201: identifier 'TOTAL_CRUSHING@ORACLEDB>@HO' must be
declared

The trigger I created is given below:

create or replace trigger ins_crushing_figure_uday after insert or update on weight_slip

declare

u_SLIP_NO WEIGHT_SLIP.SLIP_NO%type;
yrcode varchar2(9);
plantcode number(1);
totcrush number(10,3);
ondate date;

begin

if inserting then

yrcode :='2009-2010';
plantcode:=1;
totcrush:=0;
select sum(net_weight_farmer) into totcrush from weight_slip where weight_out>0;
select max(weight_out_date) into ondate from weight_slip where weight_out>0;
delete from total_Crushing@oracledb>@ho where plant_code=plantcode;
insert into total_Crushing@oracledb>@ho values(plantcode,yrcode,totcrush,sysdate);

end if;

if updating then

yrcode :='2009-2010';
plantcode:=1;
ondate:=sysdate;
totcrush:=0;
select sum(net_weight_farmer) into totcrush from weight_slip where weight_out>0;
select max(weight_out_date) into ondate from weight_slip where weight_out>0;
delete from total_Crushing@oracledb>@ho where plant_code=plantcode;
insert into total_Crushing@oracledb>@ho values(plantcode,yrcode,totcrush,sysdate);

end if;

end;

/



total_crushing is table. It is created on both local and remote database. When this table gets inserted or updated the remote table has to be inserted or updated.


Please reply fast.

Thanks in Advance.
Re: Trigger Error [message #439082 is a reply to message #439078] Thu, 14 January 2010 02:21 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do really have a database link named "oracledb>@ho"?

Post "select * from user_db_links;"

Before please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel

[Updated on: Thu, 14 January 2010 02:36]

Report message to a moderator

Re: Trigger Error [message #439084 is a reply to message #439082] Thu, 14 January 2010 02:33 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I strongly doubt that thatis the name of the DB link:
SQL> create database link oracledb>@ho connect to dev ientified by dev using 'DEV10G';
create database link oracledb>@ho connect to dev ientified by dev using 'DEV10G'
                             *
ERROR at line 1:
ORA-00933: SQL command not properly ended


SQL> create database link "oracledb>@ho" connect to dev ientified by dev using 'DEV10G';
create database link "oracledb>@ho" connect to dev ientified by dev using 'DEV10G'
                     *
ERROR at line 1:
ORA-02083: database name has illegal character '>'
Re: Trigger Error [message #439095 is a reply to message #439084] Thu, 14 January 2010 04:07 Go to previous messageGo to next message
studydumps
Messages: 2
Registered: January 2010
Location: india
Junior Member
Sorry this is printing mistake">"

actually the statement is
total_Crushing @ oracledb @ ho

Regards,

Uday

[Updated on: Thu, 14 January 2010 04:11]

Report message to a moderator

Re: Trigger Error [message #439097 is a reply to message #439095] Thu, 14 January 2010 04:10 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And the same remarks apply.

Regards
Michel

[OP modifies his last post when I was writting]

[Updated on: Thu, 14 January 2010 04:27]

Report message to a moderator

Re: Trigger Error [message #439098 is a reply to message #439078] Thu, 14 January 2010 04:20 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
What happens if you do this in sqlplus on the local db?
select * from total_Crushing@oracledb@ho where rownum = 1
Re: Trigger Error [message #439100 is a reply to message #439095] Thu, 14 January 2010 04:29 Go to previous message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
studydumps wrote on Thu, 14 January 2010 11:07
Sorry this is printing mistake">"

actually the statement is
total_Crushing @ oracledb @ ho

Regards,

Uday

This is why you must not just explain what you did but copy and paste your SQL*Plus session.
Now we don't know what you actually did and what you actually got.

Regards
Michel

Previous Topic: create a autorunning trigger..(merged)
Next Topic: Multi-Language Charachters & XML Error!
Goto Forum:
  


Current Time: Sat Sep 24 19:47:08 CDT 2016

Total time taken to generate the page: 0.04250 seconds