What I'm looking to do is update a table based on 1 column from another
table.
The table I need to extract data from has a primary key but not on the
column I need. The column I need could have 200 rows of the same data.
However the table that I want to update can only have distinct values
for that column.
I tried to add a primary key to the table that is being updated but it
returns an error when the trigger fires because it violates the primary
key constraint. So that doesn't work.

I thought maybe create a view for the column I need using the distinct
keyword and then making a trigger based on the view but that hasn't work
either maybe because my syntax is wrong.

CREATE OR REPLACE TRIGGER VIEW_T1
INSTEAD OF INSERT
ON VIEW_T1
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
BEGIN
INSERT INTO T2(COL1) VALUES (:NEW.COL1);
END ;
/

The trigger created fine but doesn't seem to fire to update the t2 table
at all.



-----Original Message-----
From: Tina Massmann
[mailto:oracledba-ezmlmshield-x64613373.[Email address protected]
Sent: Thursday, May 03, 2007 1:17 PM
To: LazyDBA Discussion
Subject: Create trigger that updates table

I created 2 tables T1 & T2 with the same columns (col1 number(2), col2
number(2)).

What I would like to do is create an after insert trigger some how using
distinct keyword on col1.
Is there a way of doing this?

So say I insert the following into T1:

Insert into T1 (col1, col2) values (1, 1);
Insert into T1 (col1, col2) values (1, 2);
Insert into T1 (col1, col2) values (1, 3);
Insert into T1 (col1, col2) values (2, 2);
Insert into T1 (col1, col2) values (3, 1);

What I would like added to T2 col1 is:

1
2
3

I created a simple trigger but can't figure out how to add distinct to
it:

CREATE OR REPLACE TRIGGER INSERT_T2
AFTER INSERT
ON T1
FOR EACH ROW
BEGIN
INSERT INTO T2(COL1) VALUES(:NEW.COL1);
END;





Please consider the environment before printing this email *

Confidentiality Notice: This email transmission may contain confidential
or legally privileged information that is intended only for the
individual or entity named in the e-mail address. If you are not the
intended recipient, you are hereby notified that any disclosure,
copying, distribution, or reliance upon the contents of this e-mail is
strictly prohibited. If you have received this e-mail transmission in
error, please reply to the sender, so that arrangements can be made for
proper delivery, and then please delete the message from your in-box.




---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html



Oracle LazyDBA home page