Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2010
    Location
    Kandy, Sri Lanka
    Posts
    3

    Unanswered: Triggers > Before Insert Help

    Hi,

    Tables
    ===========
    Admission(Aid,Patient_id,Operation_code)
    Operation(Operation_code, Op_name, Op_fee)
    Admission_New(Aid,Patient_id,Op_fee)

    Constraints
    ===========
    Admission(Operation_code) references Operation(Operation_code)

    What I want to do
    ===========
    Every time a new record being INSERTED in to Admissions, I want to insert relevant details to the Admission_New table with the relevant Op_fee from Operation table.

    What I did
    ===========

    Code:
    create or replace trigger trigPatientPay
    before insert on Admission
    for each row
    
    declare
    	taid number;
    	tpid number;
    	tfee number;
    	top_code varchar2(2);
    
    begin
    
    	select Aid, Patient_id, Operation_code 
    	into taid, tpid, top_code 
    	from Admission
    	where Admission_id=:new.Admission_id;
    
    	select Ope_fee into tfee
            from Operation 
            where Op_code=top_code;
    
    	insert into Admission_New values( taid, tpid, tfee);
    
    end trigPatientPay;
    Trigger creation is ok.

    But when I try to insert a new record to Admission
    Code:
     insert into Admission values(110,999,'HT');
    Error occurs

    ERROR at line 1:
    ORA-01403: no data found
    ORA-06512: at "GAYAN.TRIGPATIENTPAY"
    ORA-04088: error during execution of trigger 'GAYAN.TRIGPATIENTPAY'

    Please help

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by gayan84 View Post
    ORA-01403: no data found
    This exception is thrown by your SELECT ... INTO because the new row does not yet exist.

    You don't need to use SELECT to access values from the inserted row in a trigger.

    I guess you want something like this:
    Code:
    create or replace trigger trigPatientPay
    before insert on Admission
    for each row
    
    declare
    	tfee number;
    begin
    	select Ope_fee 
            into tfee
            from Operation 
            where Op_code=:NEW.Operation_code ;
    
    	insert into Admission_New values( taid, tpid, tfee);
    
    end trigPatientPay;
    But you still need to handle the NO_DATA_FOUND exception for the remaining select. Check out the PL/SQL manual to learn how to handle exceptions.

    And you should always state the columns that you are using in an INSERT statement. Using an INSERT without specifying a column list is begging for problems.

    So instead of
    Code:
    insert into Admission_New values( taid, tpid, tfee);
    you should write
    Code:
    insert into Admission_New (aid, patient_id, ope_fee) values( taid, tpid, tfee);
    or whatever the correct column names for the admission_new table are.

  3. #3
    Join Date
    Jul 2010
    Location
    Kandy, Sri Lanka
    Posts
    3
    hi shammat,

    Thanks a lot for the response. That helped a bit, but didn't solve my problem. I need to the insert the values which are being inserted to the Admission table to Admission_New table.

    Whenever you add a record to the Admission table, values of columns Aid and Person_id should get copied to the Admission_New table with the corresponding Op_fee value from Operation table.

    **Your solution provides answer to Op_fee. Thanks for that.
    ** But how to insert the new data to the Admission_New table

    Thanks in advance

  4. #4
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by gayan84 View Post
    But how to insert the new data to the Admission_New table
    Using the INSERT statement that you already have, you just need to provide the correct values (see my example)

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    REFERENCING [NEW AS ....

    also, why perform the select then insert? Why not just insert with a select?

    Code:
       ...trigger ddl with referencing new as n...
    insert into Admission_New (aid, patient_id, ope_fee) 
    values( select n.taid, n.tpid, op.Ope_fee 
            from Operation op
            where Op_code= n.Operation_code);
    Dave

  6. #6
    Join Date
    Jul 2010
    Location
    Kandy, Sri Lanka
    Posts
    3

    Smile A big thank you

    hi shammat & dave,

    thanks a lot for the responses. With your advices I solved it easily.


    keep up the great work

    Gayan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •