• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 432
  • Last Modified:

ORACLE Trigger

Hi,
I created two tables...
CREATE TABLE "HR_HR"."auser_gen"
   (      "user_code" NUMBER(10,0),
      "gender" NUMBER(10,0),
      "date_of_birth" DATE,
      "country_of_birth" NUMBER(10,0),
      "nationality" NUMBER(10,0),
      "place_of_birth" VARCHAR2(100 CHAR),
      "officer_picture" BLOB,
      "auser_ID" NUMBER(5,0) NOT NULL ENABLE,
      "name" VARCHAR2(150),
      "surname" VARCHAR2(150),
      "given_name" VARCHAR2(150),
       PRIMARY KEY ("auser_ID")
      
       CREATE TABLE "HR_HR"."user_adrress"
   (      "primary_key" NUMBER(10,0),
      "user_code" NUMBER(10,0),
      "town_village" VARCHAR2(250),
      "region_municipality" VARCHAR2(250),
      "street" VARCHAR2(250),
      "house_number" VARCHAR2(20),
      "type_of_address" NUMBER(10,0),
       PRIMARY KEY ("primary_key")

And sequences seq_auser_gen and seq_user_adrress..

I need trigger to insert new primary_key and user_code in user_adrress whenever new record is entered in  auser_gen.
TX
0
andrejaTJ
Asked:
andrejaTJ
  • 3
  • 3
1 Solution
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
A possible trigger could look like this:
create or replace trigger tr_auser_gen_aier

  after insert on "HR_HR"."auser_gen"
  referencing new as new old as old
  for each row

begin

  insert into "HR_HR"."user_adrress"
  values
    (<new value for "primary_key">,
     <new value for "user_code">,
     <new value for "town_village">,
     <new value for "region_municipality">,
     <new value for "street">,
     <new value for "house_number">,
     <new value for "type_of_address">;

exception
  when others then
    dbms_output.put_line(dbms_utility.format_error_stack()); -- here goes your exception handling

end;

Open in new window


But where do you want to take the new values from?!?
0
 
andrejaTJAuthor Commented:
"HR_HR"."user_adrress" primary_key sequence

"HR_HR"."user_adrress" user code from "HR_HR"."auser_gen" "user_code"

When in application I'm entering the new record in "HR_HR"."user_adrress", to get sequence number in  primary_key "HR_HR"."user_adrress"  "primary_key"
and "HR_HR"."auser_gen" "user_code" in  "HR_HR"."user_adrress" user code
TX
0
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
Sorry andrejaTJ, but your last comment is somehow confusing me... Quite weird.. please try to explain more "clearly" ;-)
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
andrejaTJAuthor Commented:
1. When record is entered in the  "HR_HR"."auser_gen", the auser_ID shall get new sequence number (auto number)

2. "HR_HR"."auser_gen" "primary_key" shall get new sequence number (auto number)

3. "HR_HR"."user_adrress" "primary_key" shall get new sequence number (auto number)

4.  "HR_HR"."auser_gen" "user_code" shall be inserted in "HR_HR"."user_adrress" "user_code"

I hope is more clear now :)
TX
0
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
create or replace trigger tr_auser_gen_bier

  before insert on "HR_HR"."auser_gen"
  referencing new as new old as old
  for each row

begin

  select seq_auser_gen.nextval into :new.auser_ID;
  /* depending on your Oracle version you may also do this: */
  /* :new.auser_ID := seq_auser_gen.nextval; */ 

  insert into "HR_HR"."user_adrress"
    ( primary_key,
      user_code)
    select primary_key.nextval,
               :new.user_code
      from dual;

exception
  when others then
    RAISE_APPLICATION_ERROR(-20001, 'Insert failed!' || sqlerrm());
end;

Open in new window

0
 
andrejaTJAuthor Commented:
TX
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now