Re: Trigger help for Oracel newbie

From: Robert Brown <rob_99_at_my-deja.com>
Date: Tue, 03 Oct 2000 17:45:52 GMT
Message-ID: <8rd60e$445$1@nnrp1.deja.com>

Thanks Sybrand!

It seems that the 'new' data is a set of variables in Oracle, in Sybase it is effectively a temp table, hence my error.

Thanks again,

Rob

In article <970177971.14345.3.pluto.d4ee154e_at_news.demon.nl>,   "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote:
> 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.
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Oct 03 2000 - 12:45:52 CDT