hi all,
i am writing a trigger for update.while i am executing the query i am getting no data found error.could anyone help me how to solve this query.
thanks in advance,
sobha.
Printable View
hi all,
i am writing a trigger for update.while i am executing the query i am getting no data found error.could anyone help me how to solve this query.
thanks in advance,
sobha.
Does your code includes a "select into" statement?
Most probably you are getting ORA-01403 because of "select into" statement is returning nothing. Either fix your query to ensure a single value is returned or fix your code not to execute "select into" when you know no result is going to be returned.
hi PAVB,
i have used Select into.my query is below.what should i use instead of into not get get the no data found error.
create or replace trigger exp_lc_document_txn_status_trg
after update on exp_lc_document_trg
for each row
declare
-- local variables here
workflowID varchar2(16);
begin
select workflow.r_object_id into workflowID
from dm_workflow_s workflow,
dmi_package_s packs,
dmi_package_r packr
where
workflow.r_object_id=packs.r_workflow_id
and packs.r_object_id=packr.r_object_id
and packr.r_component_id=:old.r_object_id
and workflowID <> ' ';
if (:new.TRANSACTION_STATUS <> :old.TRANSACTION_STATUS) THEN
INSERT INTO T_REP_TXN_STATUS
VALUES
(
workflowID,
:old.transaction_status,
sysdate,
sysdate
) ;
end if;
commit;
end exp_lc_document_txn_status_trg;
The issue is restricted to query below; I don't know your business specs and I know nothing about the data in those tables so I cannot even tell if the query is correct or not so, I'm assuming the query is correct.
Problem is query is returning no rows, that's why you are getting ORA-01403.
I would suggest to test the query independently and be sure it always returns one row, no more, no less.Code:select workflow.r_object_id
into workflowID
from dm_workflow_s workflow,
dmi_package_s packs,
dmi_package_r packr
where workflow.r_object_id=packs.r_workflow_id
and packs.r_object_id=packr.r_object_id
and packr.r_component_id=ld.r_object_id
and workflowID <> ' ';