SQL & PL/SQL

Hello,

I am attempting to create a trigger involving three tables. Table 1 gets a value inserted into one of its fields and the value in that field must be verified against values in Table 2. If there is a match, nothing occurs. If there is not a match, this information is logged into a third table, Table 3.

Here is my trigger so far:



CREATE OR REPLACE TRIGGER CO_SVIA_VERIFY_1
BEFORE UPDATE
OF SHIP_VIA
ON CUSTOMER_ORDER
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
WHEN (
new.ship_via in (
select code
from ship_via)
)
declare
l_action co_ship_via_verify_log_1.action%type;
begin

if UPDATING then
l_action := 'Update';
else
raise_application_error( -20001,
'You should never ever get this error.' );
end if;

if UPDATING( 'SHIP_VIA' ) then
l_action := l_action || ' - ' ||
'Ship via for order id ' || :old.id ||
' for customer id ' || :old.customer_id ||
' contains an invalid ship via value: ' || :new.ship_via;
end if;

insert into co_ship_via_verify_log_1(
who, action, event_dtime, id )
values (
user, l_action, sysdate, co_ship_via_verify_log_seq1.nextval );

end;



I am receiving an ORA-02251 at line 9 - subquery not allowed here. My research indicates that I cannot be utilizing a subquery in the when section of the trigger. I understand this limitation, but I have so far not been able to rewrite my trigger so that I can utilize the subquery to complete the verification of the value entered into Table 1 against values in Table 2.

Can anyone direct me down the correct path to accomplish this?

Thank you.