| Update trigger [message #38816] |
Thu, 16 May 2002 13:06  |
Toyn
Messages: 36
Registered: April 2001
|
Member |
|
|
Hi,
I'm having issues with a trigger I created. I have 2 values (order_num, trans_code). The only way I can have duplicate order_num in my source is if the first trans_code is 'O', and the second is 'C' (for ordered/canceled). I want to insert/update orders_test with my source table. In orders_test, I only want a single instance of order_num for each order. If the first insert has a trans_code value of 'O', then I want the trigger to continue on to the next insert. However, if the insert encounters an identical order_num in the destination and the source trans_code = 'C', I want the orders_test.trans_code updated to 'C' for that particular order_num. Below is what I've come up with so far. Any one have a suggestion what's wrong with my syntax? Thanks.
CREATE OR REPLACE TRIGGER ord_canc_update
before insert or update on ORDERS_TEST
for each row
begin
if updating then
if :new.ORDER_NUM = ORDER_NUM and :old.TRANS_CODE = 'C' then
set TRANS_CODE = 'C'
elseif inserting
set TRANS_CODE = :new.TRANS_CODE
END IF;
END;
|
|
|
|
| Re: Update trigger [message #38819 is a reply to message #38816] |
Thu, 16 May 2002 17:10  |
Tony
Messages: 190
Registered: June 2001
|
Senior Member |
|
|
You won't be able to handle this type of logic in a trigger. Instead, since you are using a source table to update orders_test, how about just formatting an insert/select into statement that accomplishes the same thing?
Basically, if an order_num exists once in the source table, you know it has a trans_code of 'O' and should be inserted as such in the target table. If an order_num exists twice in source, it should be inserted as a 'C' in the target table. Does this match your logic?
If so, then if your source table looks like:
ORDER_NUM TRANS_CODE
1 O
2 C
2 O
3 O
4 C
4 O
an insert/select into your orders_test by:
insert into orders_test
select order_num, min(trans_code)
from source
group by order_num;
would result in orders_test looking like:
ORDER_NUM TRANS_CODE
1 O
2 C
3 O
4 C
Everything done in SQL - no PL/SQL or triggers required.
|
|
|
|
| Re: Update trigger [message #38827 is a reply to message #38819] |
Fri, 17 May 2002 07:35 |
Toyn
Messages: 36
Registered: April 2001
|
Member |
|
|
Well, I should have mentioned this earlier, but there are several other fields that get inserted initially when an order is placed, specifically a textual description column. I was trying to work something like this:
On insert
If source.order_num=orders_test.order_num and source.trans_code = 'C' then
set orders_test.trans_code = 'C';
This would retain the other information inserted into the orders_test table. That's why I was thinking I needed the trigger.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
