Re: trigger help

From: Jomarlen <jomarlen_at_aol.com>
Date: 1997/09/30
Message-ID: <19970930203901.QAA05265@ladder01.news.aol.com>#1/1

>Help!

Aside from the fact that you are selecting a value into recnum then doing nothing with it, the glaring error is in the INSERT section.

values (:old.seqno, :old.location, :old.dob, :old.sex,

             :old.medrec, :old.race, sysdate);

all these values are NULL - where would you get old values from for a new record

John

---

John C. Lennon
Utility Partners Inc.
4300 West Tropicana Blvd LVO-UPL
Las Vegas NV 89103

FOR UTILITY PARTNERS EMPLOYMENT
OPPORTUNITIES VISIT www.utilpart.com

e-mail: jomarlen_at_aol.com
Web Site: http://members.aol.com/jomarlen/

The views expressed in this message
are those of the author and not
necessarily those of U.P. Inc.
and/or its employees.

---

>Don't even know where to start with this one:
>
>I've got an after update/insert trigger on table referral. Basically, it
>checks the STATUS field and moves certain bits of data from PRE to POST.
>(Yes, yes, I know, but normalization isn't appropriate in this
>circumstance...)
>
>This trigger compiles just fine. It refuses to work at run time. In
>SQL*Plus, I update the status in PRE to 'X' but no data gets copied to
>POST. When Access users (who are linked via ODBC to these tables) update
>the status field, they get an ODBC update failed error.
>
>What the...?
>
>Where do I start looking, or do you see a glaring error in the code below?
>
>MANY MANY thanks in advance.
>
>-John
>
>create or replace trigger TRIGAU_PRE
>after insert or update of STATUS on PRE
>for each row
>
>declare
> recnum number := 0;
>
>begin
> if updating ('STATUS') and :new.STATUS = 'X' then
> select count(*) into recnum
> from POST
> where seqno = :old.seqno;
> recnum := recnum + 1;
>
> insert into POST
> (seqno, location, dob, sex, medrec, race, posting_dt)
> values (:old.seqno, :old.location, :old.dob, :old.sex,
> :old.medrec, :old.race, sysdate);
> end if;
>
> if inserting and :new.STATUS = 'X' then
> select count(*) into recnum
> from POST
> where seqno = :new.seqno;
> recnum := recnum + 1;
>
> insert into POST
> (seqno, location, dob, sex, medrec, race, posting_dt)
> values (:old.seqno, :old.location, :old.dob, :old.sex,
> :old.medrec, :old.race, sysdate);
> end if;
>
>end;
>
>
>
>
>
>
>
>
>
>
Received on Tue Sep 30 1997 - 00:00:00 CDT