Is there a way in the trigger to dismiss exception raised?
Reason I ask is because I tried this when I try to add a duplicate to
the T1 table the trigger fires an error and inserted row is not updated
in either tables (T1 or T2). Seems weird to me since the trigger should
fire after the insert but seems to be firing before.

-----Original Message-----
From: William J Koster
[mailto:oracledba-ezmlmshield-x48454621.[Email address protected]
Sent: Thursday, May 03, 2007 2:59 PM
To: LazyDBA Discussion
Subject: Re: Create trigger that updates table

So go ahead and let your trigger do the insert into T2, and just dismiss
the exception that'll be
raised if that value is already defined in T2.

If for some reason the column in T2 doesn't have a unique constraint on
it,
then your T1 trigger
should probably just query T2 to determine if a row with that value
exists.
If it doesn't, the
trigger can insert it; if it does, the trigger just passes ...


------------------------------------------------------------------------
------------------------------------------------------------------------
--------------------------------

This is a PRIVATE message. If you are not the intended recipient, please
delete without copying and kindly advise us by e-mail of the mistake in
delivery. NOTE: Regardless of content, this e-mail shall not operate to
bind CSC to any order or other contract unless pursuant to explicit
written
agreement or government initiative expressly permitting the use of
e-mail
for such purpose.
------------------------------------------------------------------------
------------------------------------------------------------------------
--------------------------------






"jpmdeveloper"

<oracledba-ezmlms

hield-x33814632.x
To
[Email Address Removed] "LazyDBA Discussion"

a.com> <[Email address protected]


cc
05/03/2007 02:52

PM
Subject
Re: Create trigger that updates

table


















Well I am not sure what it is exactly you want to do, but if you have a
primary key or unique constraint on the column then you will never have
duplicate values.

----- Original Message -----
From: Tina Massmann
Date: Thursday, May 3, 2007 1:17 pm
Subject: Create trigger that updates table
To: LazyDBA Discussion

> 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
>
>
>


---------------------------------------------------------------------
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






---------------------------------------------------------------------
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