ORACLE Trigger

Posted on 2014-01-21
Last Modified: 2014-01-28
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.
Question by:andrejaTJ
  • 3
  • 3
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


  insert into "HR_HR"."user_adrress"
    (<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">;

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


Open in new window

But where do you want to take the new values from?!?

Author Comment

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
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" ;-)
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.


Author Comment

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 :)
LVL 13

Accepted Solution

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


  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,
    select primary_key.nextval,
      from dual;

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

Open in new window


Author Closing Comment

ID: 39815077

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

I recently found myself in a Corporate Situation where the client had requested blocking access to any and all websites except his own Domain? Easy? I am sure this would be your answer but their requirement was, this has to be done without using…
Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

920 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

16 Experts available now in Live!

Get 1:1 Help Now