Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

ORACLE Trigger

Posted on 2014-01-21
6
Medium Priority
?
424 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 14

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 14

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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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 14

Accepted Solution

by:
Alexander Eßer [Alex140181] earned 800 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 to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This Micro Tutorial will demonstrate how nuggets on the Web are formatted by using Chrome Developer Tools. These tools would not only view the site's CSS but it can also modify it and save the CSS to use on your own site.

722 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