Solved

ORACLE Trigger

Posted on 2014-01-21
6
398 Views
Last Modified: 2014-01-28
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
Comment
Question by:andrejaTJ
  • 3
  • 3
6 Comments
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39799332
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
 

Author Comment

by:andrejaTJ
ID: 39799537
"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
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39799559
Sorry andrejaTJ, but your last comment is somehow confusing me... Quite weird.. please try to explain more "clearly" ;-)
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:andrejaTJ
ID: 39799603
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
 
LVL 13

Accepted Solution

by:
Alexander Eßer [Alex140181] earned 200 total points
ID: 39799850
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
 

Author Closing Comment

by:andrejaTJ
ID: 39815077
TX
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Do you come here a lot? Are you lazy like me and don't want to go through the "trouble" of having to click your Dock's Safari icon and then having to click your Experts Exchange Favorites bookmark to get here? Well then this article is for you.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This tutorial explains how to use the VisualVM tool for the Java platform application. This video goes into detail on the Threads, Sampler, and Profiler tabs.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now