Home » SQL & PL/SQL » SQL & PL/SQL » delete with trigger (merged many same question)
delete with trigger (merged many same question) [message #436388] Wed, 23 December 2009 05:14 Go to next message
anat1983
Messages: 6
Registered: December 2009
Junior Member
hello!
i have my trigger that should delete data from sql server table after delete oracle table.
when i trying to delete from oracle table i have error:
SQL> delete from cust.SUBSCRIBER;
delete from cust.SUBSCRIBER
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "PROC.AFTER_INSERT_SUB", line 12
ORA-04088: error during execution of trigger 'PROC.AFTER_INSERT_SUB'

i have a data on 2 tables:
this is my 2 tables:
--oracle table
SQL> select * from cust.SUBSCRIBER;

SUB_ID CUST_ID SUB_NAME SUB_LAST_NAME SU FEATURE_ID PACKAGE_ID ADRESS_ID
---------- ---------- ------------------------- ------------------------- -- ---------- ---------- ----------
544444445 8 Rivka Malik f 2 3 4

-sql table:

SQL> select * from billing.pack_sub@billing;

sub_id package_cost phone_cost sms_cost multi_cost
---------- ------------ ---------- ---------- ----------
544444445 7 .5 .5 2

my trigger:
create or replace trigger after_insert_sub
AFTER INSERT OR UPDATE OR DELETE ON cust.subscriber
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
v_package_cost number;
v_phone_cost number;
v_sms_cost number;
v_multi_cost number;
BEGIN
select package_cost, phone_cost, sms_cost, internet_cost into v_package_cost, v_phone_cost, v_sms_cost, v_multi_cost from cust.packages
where package_id = :new.package_id;
if INSERTING THEN
insert into billing.pack_sub@billing
values (:new.sub_id, v_package_cost, v_phone_cost, v_sms_cost, v_multi_cost);
commit;
elsif UPDATING ('package_id') THEN
UPDATE billing.pack_sub@billing set "sub_id" = :new.sub_id,
"package_cost" = v_package_cost,
"phone_cost" = v_phone_cost,
"sms_cost" = v_sms_cost,
"multi_cost" = v_multi_cost;
ELSIF DELETING THEN
DELETE FROM billing.pack_sub@billing where "sub_id" = :old.sub_id);
end if;
end;

Re: delete with trigger [message #436390 is a reply to message #436388] Wed, 23 December 2009 05:32 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Your trigger does this:
select package_cost, phone_cost, sms_cost, internet_cost into v_package_cost, v_phone_cost, v_sms_cost, v_multi_cost 
from cust.packages
where package_id = :new.package_id;


What do you think :NEW.PACKAGE_ID will be when you're deleting?

Hint: :OLD.PACKAGE_ID is the value of package_id BEFORE the delete.

[Updated on: Wed, 23 December 2009 05:43] by Moderator

Report message to a moderator

Re: delete with trigger [message #436392 is a reply to message #436388] Wed, 23 December 2009 05:37 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Better to have an IF for insert/delete/update before the select and assign the new/old package_id to a variable.
Then select the required and use it in the next IF.

By
Vamsi
Re: delete with trigger [message #436394 is a reply to message #436388] Wed, 23 December 2009 05:44 Go to previous messageGo to next message
anat1983
Messages: 6
Registered: December 2009
Junior Member
thank you!!!
that work
error [message #436407 is a reply to message #436388] Wed, 23 December 2009 07:35 Go to previous messageGo to next message
anat1983
Messages: 6
Registered: December 2009
Junior Member
hello!
i am trying to sumbit this:
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
a cust.subscriber.cust_id%type;
b cust.features.feature_id%type;
c number; --cust.packages.package_id%type;
d cust.adresses.adress_id%type;
CURSOR cust_cur
IS
select cust_id into a FROM (SELECT cust_id
FROM cust.customers
ORDER BY dbms_random.value);
CURSOR cust_feat
IS
select feature_id into b FROM (SELECT feature_id
FROM cust.features
ORDER BY dbms_random.value);
CURSOR cust_pack
IS
select package_id into c FROM (SELECT package_id
FROM cust.packages
ORDER BY dbms_random.value);
CURSOR cust_adress
IS
select adress_id into d FROM (SELECT adress_id
FROM cust.adresses
ORDER BY dbms_random.value);

begin
Insert into cust.SUBSCRIBER Values (0544444445, a, 'Rivka' , 'Malik', 'ss', b, c, d);
commit;
insert into cust.SUBSCRIBER Values (0547487632, a, 'Rachel' , 'Shwartzman', 'dd', b, c, d);
--end loop;
commit;
end;

i have error:
ERROR at line 1:
ORA-01403: no data found
ORA-01403: no data found
ORA-06512: at "PROC.AFTER_INSERT_SUB", line 12
ORA-04088: error during execution of trigger 'PROC.AFTER_INSERT_SUB'
ORA-06512: at line 29


my triiger is:
create or replace trigger after_insert_sub
AFTER INSERT OR UPDATE OR DELETE ON cust.subscriber
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
v_package_cost number;
v_phone_cost number;
v_sms_cost number;
v_multi_cost number;

BEGIN
if INSERTING THEN
select package_cost, phone_cost, sms_cost, internet_cost into v_package_cost, v_phone_cost, v_sms_cost, v_multi_cost from cust.packages
where package_id = :new.package_id;
insert into billing.pack_sub@billing
values (:new.sub_id,v_package_cost, v_phone_cost, v_sms_cost, v_multi_cost);
commit;
elsif UPDATING ('package_id') THEN
UPDATE billing.pack_sub@billing set "sub_id" = :new.sub_id,
"package_cost" = v_package_cost,
"phone_cost" = v_phone_cost,
"sms_cost" = v_sms_cost,
"multi_cost" = v_multi_cost;
commit;
ELSIF DELETING THEN
select package_cost, phone_cost, sms_cost, internet_cost into v_package_cost, v_phone_cost, v_sms_cost, v_multi_cost from cust.packages
where package_id = :old.package_id;
DELETE FROM billing.pack_sub@billing where "sub_id" = :old.sub_id;
commit;
end if;
end;


can somebody help?
thanks
Re: error [message #436410 is a reply to message #436407] Wed, 23 December 2009 07:42 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Why have you started a new thread for this?

The error is most likely caused by there not being a row in the Packages table for the specified value of package_id.

That code at the top is so full of bugs it's painful to look at.

I frankly don't believe that the piece of code you posted has ever run.
Re: help [message #436411 is a reply to message #436388] Wed, 23 December 2009 07:43 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
STOP POSTING THE SAME QUESTION AGAIN AND AGAIN!!!!!!
Re: error [message #436414 is a reply to message #436388] Wed, 23 December 2009 07:48 Go to previous messageGo to next message
Michel Cadot
Messages: 63812
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
ORA-01403: no data found

No data was found at line 12

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.
Also always post your Oracle version with 4 decimals.

Regards
Michel
Re: help [message #436415 is a reply to message #436388] Wed, 23 December 2009 07:49 Go to previous messageGo to next message
Michel Cadot
Messages: 63812
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
YES stop to do it and read OraFAQ Forum Guide.

Regards
Michel
Re: help [message #436418 is a reply to message #436388] Wed, 23 December 2009 07:51 Go to previous message
Michel Cadot
Messages: 63812
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
DIDN'T YOU HEAR WHAT WE SAID?

STOP

AND READ FORUM GUIDE
Previous Topic: create a view that has zip codes and nearby zip codes
Next Topic: Getting one line from table per person
Goto Forum:
  


Current Time: Fri Sep 30 10:44:30 CDT 2016

Total time taken to generate the page: 0.21860 seconds