| Trigger - using select into [message #36255] |
Tue, 13 November 2001 09:28  |
C. Kelley
Messages: 1
Registered: November 2001
|
Junior Member |
|
|
DROP TRIGGER U_TBU_CUSTORDL;
CREATE TRIGGER U_TBU_CUSTORDL
BEFORE UPDATE OF ORDER_QTY, UNIT_PRICE, ACK_ID ON CUST_ORDER_LINE
FOR EACH ROW
DECLARE TRANS_MONTH DATE; LINE_ACTION LONG; ORDER_QTY NUMBER; UNIT_PRICE NUMBER;
BEGIN
IF (:NEW.ORDER_QTY <> :OLD.ORDER_QTY OR
:NEW.UNIT_PRICE <> :OLD.UNIT_PRICE) AND
(:NEW.ACK_ID IS NULL OR :NEW.ACK_ID IN ('-','CH','OS','PR','PU','SP','SM','OU')) THEN
SELECT MIN(TRUNC(TRANSACTION_DATE,'MM')), LINE_ACTION, OLD_ORDER_QTY, OLD_UNIT_PRICE INTO TRANS_MONTH, LINE_ACTION, ORDER_QTY, UNIT_PRICE FROM U_CUST_ORDER_BOOK WHERE CUST_ORDER_ID = :OLD.CUST_ORDER_ID AND LINE_NO = :OLD.LINE_NO AND
TRUNC(TRANSACTION_DATE,'MM') = TRUNC(SYSDATE,'MM') AND TRUNC(TRANSACTION_DATE,'YY') = TRUNC(SYSDATE, 'YY') GROUP BY LINE_ACTION, OLD_ORDER_QTY, OLD_UNIT_PRICE; ....
When I run my trigger it compiles without any errors. The application that I am running it against does not like the line_action, order_qty, unit_price in the select statement as seen in the above example. It does not save the changes in either the application or the insert (that follows this code). Any ideas? It works fine if I leave these 3 fields out of the select.
----------------------------------------------------------------------
|
|
|
|
| Re: Trigger - using select into [message #36257 is a reply to message #36255] |
Tue, 13 November 2001 09:41  |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
I guess it's because LINE_ACTION is of type LONG. Remember that LONG can be upto 2GB, so it makes sense to have some restrictions.
Try defining a cursor for the select statement and then FETCH into your variables. Remember that a variable of type LONG in PL/SQL is not up to 2GB - I can't remember how big it is.
----------------------------------------------------------------------
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
