Re: Trigger help for Oracel newbie

 CREATE OR REPLACE TRIGGER sgus_cusip_to_ticketno  BEFORE INSERT OR UPDATE OF ticket_no ON deals  FOR EACH ROW
 BEGIN
  SELECT secissue.cusip
 into :new.ticketno
 FROM secissue
 WHERE secissue.thekey = :new.secissue
 END;
The purpose of triggers is usually to populate columns of the *current* table using complex rules.
The new values of the columns are referred to as :new.<column name> the old ones as :old.<column name>.
You can assign new values to your columns *directly* (instead of using update, which isn't allowed either, as the table is *already* participating in a transaction)
Please refer to the application developers guide for further details on triggers. If you don't have it, it's online at http://technet.oracle.com

"Robert Brown" <rob_99_at_my-deja.com> wrote in message news:8qvo40$6ei$1_at_nnrp1.deja.com...
> Hi,
>
> I've been using Sybase for a few years and now I'm working on Oracle,
> so I need some help with translation.
>
> In particular, the following Create Trigger is giving me 'MGR-00072:
> Warning: TRIGGER SGUS_CUSIP_TO_TICKETNO created with compilation errors.
> ' error message:
>
> CREATE OR REPLACE TRIGGER sgus_cusip_to_ticketno
> BEFORE INSERT OR UPDATE OF ticket_no ON deals
> FOR EACH ROW
> BEGIN
> UPDATE :new
> SET ticket_no = ( SELECT secissue.cusip
> FROM secissue
> WHERE secissue.thekey = :new.secissue)
> END;
>
> I then ran:
> select Line, Position, Text from user_errors
> where Name = 'SGUS_CUSIP_TO_TICKETNO'
>
> ... which gives me:
> LINE POSITION
> TEXT
>
> ---------- ---------- --------------------------------------------------
> ------------------------------
> 2 9 PLS-00049: bad bind
> variable 'NEW'
> 2 9 PLS-00103: Encountered the symbol "" when
> expecting one of the following:
>
> (
> 6 1 PLS-00103: Encountered the symbol "END" when
> expecting one of the following:
>
>
> 3 rows selected.
>
>
> I must admit I'm pretty lost - I thought :new was the correct way of
> addressing the row about to be inserted or updated? That 'expected one
> of the following:' is odd. What following?
>
>
> Any help would be most appreciated,
>
>
> Rob
> p.s. is there an Oracle equivelent of Sybases 'sp_help'?
> p.p.s. is there any 'Oracle for Sybase users' resource out there?
>
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Thu Sep 28 2000 - 16:51:11 CDT