Re: Concatenating and copying columns data

On 9 Apr 2007 06:56:45 -0700, dustin.parkman_at_bentley.com wrote:

>Sounds like all you are trying to do is the following. This is just
>pseudo code so you will have to verify that it actually operates
>correctly on your end, but this should give you get you going in the
>right direction. You will need to add some validation for column B.
>It's typically bad practice to do this in a trigger, but if you have
>to you should look at EXCEPTIONS(VALUE_ERROR).
>
>CREATE TRIGGER YourTriggerName AFTER INSERT or UPDATE
>BEGIN
> UPDATE YourTableName A Set A.C = ASCII(A.A) + A.BWHERE A.ID =
>new.ID;
>END;
>
>OR
>
>CREATE TRIGGER YourTriggerName AFTER INSERT or UPDATE
>BEGIN
>/*Check to see if column B can be be cast to a numeric. If so update
>column C. If not throw an exception.
> UPDATE YourTableName A Set A.C = ASCII(A.A) + TO_CHAR(TO_
>NUMBER(A.B)) WHERE A.ID = new.ID;
> EXCEPTIONS
> WHEN VALUE_ERROR
> //Do Something
>END;
Please do not spoon feed the OP.
Apart from that, tasks like this shouldn't be addressed by coding extra redundant updates, but by simple assignments in a BEFORE INSERT or UPDATE for each row trigger.
The code you posted is
a) incorrect, because it fires only once per update b) inefficient, because it requires extra redundant updates, and consequently increases the transaction volume.

-- 
Sybrand Bakker
Senior Oracle DBA