?
Solved

Create a Trigger to insert data from 1 table to another....

Posted on 2007-10-17
4
Medium Priority
?
3,468 Views
Last Modified: 2013-12-19
I am trying to create a trigger that works after the insert of a record on one table; then goes to another table grabs data for the matching key column and inserts it into a 3rd table to create a new record. The tables are spaital but that shouldn't have any bearing on this. My insert statement works on it's own. Here's my code:

CREATE OR REPLACE TRIGGER TITLE_ABSTRACT_CREATE

after

insert on abstract_pins

for each row
begin


insert into sp_title_abstracts (GID,GEOM_AREA,COMMENTS,TAG_X,TAG_Y,TAG_VALUE,TAG_SIZE,TAG_ANGLE,TAG_JUST,GEOMETRY,PIN,PRCL_GID)
select munseq_gid.nextval,GEOM_AREA,COMMENTS,TAG_X,TAG_Y,TAG_VALUE,TAG_SIZE,TAG_ANGLE,TAG_JUST,GEOMETRY,a.PIN,GID from sp_prcl b, abstract_pins a
where a.pin = B.PIN;
end;


All help is greatly appreciated.

Thanks,
Jason
0
Comment
Question by:JMCNUTT43
4 Comments
 
LVL 12

Accepted Solution

by:
jwahl earned 1000 total points
ID: 20092823
i think this should work:

CREATE OR REPLACE TRIGGER title_abstract_create
    AFTER INSERT
    ON abstract_pins
    FOR EACH ROW
BEGIN
    INSERT INTO sp_title_abstracts
    (gid, geom_area, comments, tag_x, tag_y, tag_value, tag_size, tag_angle, tag_just, geometry, pin, prcl_gid)
        SELECT munseq_gid.NEXTVAL
              ,geom_area
              ,comments
              ,tag_x
              ,tag_y
              ,tag_value
              ,tag_size
              ,tag_angle
              ,tag_just
              ,geometry
              ,a.pin
              ,gid
        FROM   sp_prcl
        WHERE  b.pin = :NEW.PIN;
END;


0
 
LVL 9

Assisted Solution

by:konektor
konektor earned 1000 total points
ID: 20092885
you cannot perform any action (select, insert, update, delete) on the table for which is trigger defined! you cannot select from table abstract_pins in the body of trigger defined on table abstract_pins.
better to say, you can, but only when "FOR EACH ROW" is NOT used, for triggers without this clause you can do it. but theese triggers will fire only once per statement, not once per inserted row.

insert into a values (1) -- both "row" and "statement" trigger is fired once

insert into a select ... -- "row" trigger is fired for each inserted row, "statement" trigger only once


if you want to use values which are just inserting into table, use :NEW recordset

CREATE OR REPLACE TRIGGER TITLE_ABSTRACT_CREATE
after insert on abstract_pins
for each row
begin
insert into sp_title_abstracts (GID,GEOM_AREA,COMMENTS,TAG_X,TAG_Y,TAG_VALUE,TAG_SIZE,TAG_ANGLE,TAG_JUST,GEOMETRY,PIN,PRCL_GID)
select munseq_gid.nextval,GEOM_AREA,COMMENTS,TAG_X,TAG_Y,TAG_VALUE,TAG_SIZE,TAG_ANGLE,TAG_JUST,GEOMETRY,:NEW.PIN,GID from sp_prcl b
where B.PIN = :NEW.pin;
end;
0
 
LVL 14

Expert Comment

by:GGuzdziol
ID: 20092910
...or if you need to replicate *all* matching rows (not only for currently inserted PIN, all that satisfy join condition)

0. Create package with type TABLE OF abstract_pins.PIN%TYPE and variable (cache) of this type
1. Create before statement level trigger on abstract pins that will clear the cache
2. Create after row level trigger on abstract pins that will fill the cache (i.e. extend cache and put :NEW.PIN as last element)
3. Create after statement level trigger on abstract pins that will loop through cache and execute insert statement you currently have.
0
 

Author Comment

by:JMCNUTT43
ID: 20093286
Well you guys both submitted solutions at the same time. However Konektor had an explaination with his.  The trigger works great! I just have to remember that  :NEW recordset command as I have a lot of triggers to make.

Thanks for all of your help I'll have to give out the assisted solutions to both of you.

Thanks!
J
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses
Course of the Month17 days, 4 hours left to enroll

862 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