CODEWITHSUNDEEP
oracle-databaseoracle10g
Dhanesh
Dhanesh

Reputation: 1

Trigger in Oracle 10g

I've an relationship in which Account, Client are two tables are related with cl_ac relationship. Account has ac_id and client has c_id as primary keys and these same two keys are in cl_ac table as foreign keys. I have a trigger for primary keys in account (ac_id) and client (c_id) so that in both table i need to insert only remaining values and primary keys increment itself for each record. Now i want a trigger that inserts values in "cl_ac" table automatically when i insert values in account and client.

or else another option rather trigger that do not violate the foreign key constraint.

I tried many option but was unable to insert 2 values (c_id and ac_id) at a time in cl_ac table. Please somebody help me. It would have been better if i could insert an image for better understanding, i hope, i've put my question in words that is understandable .

Thank you.

Upvotes: 0

Views: 201

Answers (3)

Gary Myers
Gary Myers

Reputation: 35401

You might want to wrap the thing in a procedure, sort of like the pseduo code below

CREATE OR REPLACE PACKAGE BODY cli_acc IS
  TYPE tab_cli IS TABLE OF client%ROWTYPE;
  TYPE tab_acc IS TABLE OF account%ROWTYPE;
  PROCEDURE ins_cli_acc (i_tab_cli tab_cli, i_tab_acc tab_acc) IS
  BEGIN
    --insert all of i_tab_cli into CLIENT
    --insert all of i_tab_acc into ACCOUNT
    FOR i IN i_tab_cli.FIRST .. i_tab_cli.LAST LOOP
      FOR j IN i_tab_acc.FIRST .. i_tab_acc.LAST LOOP
         INSERT INTO cli_acc (cli_id, acc_id) 
         VALUES (i_tab_cli(i).cli_id, i_tab_acc(j).acc_id);
      END LOOP;
    END LOOP;
  END ins_cli_acc;
END cli_acc;

Upvotes: 0

Klas Lindbäck
Klas Lindbäck

Reputation: 33273

How would the trigger know which account to associate with which client?

You are better off inserting the relationship record from the code that creates the account/client records. That's where you have the necessary information.

Upvotes: 1

Kevin Burton
Kevin Burton

Reputation: 11936

perhaps you need to use RETURNING to get both of the values you want to insert, rather than a trigger ?

INSERT INTO account (ac_id,....) VALUES(seq_ac_id.NEXTVAL,....) RETURNING ac_id INTO return_account_id;
INSERT INTO client  (c_id,....) VALUES(seq_c_id.NEXTVAL,....) RETURNING c_id INTO return_client_id;
INSERT INTO relationship (ac_id,c_id) values(return_account_id, return_client_id);
COMMIT;

Upvotes: 1

Related Questions