Improve company productivity with a Business Account.Sign Up

x
?
Solved

ORACLE Trigger

Posted on 2014-01-21
6
Medium Priority
?
435 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 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
Managing Security & Risk at the Speed of Business

Gartner Research VP, Neil McDonald & AlgoSec CTO, Prof. Avishai Wool, discuss the business-driven approach to automated security policy management, its benefits and how to align security policy management with business processes to address today's security challenges.

 

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

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

SSL stands for “Secure Sockets Layer” and an SSL certificate is a critical component to keeping your website safe, secured, and compliant. Any ecommerce website must have an SSL certificate to ensure the safe handling of sensitive information like…
Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

579 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