Re: trigger help

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/09/30
Message-ID: <3432654b.26736024@newshost>#1/1

On 30 Sep 1997 18:40:45 GMT, "H. John C. Hopkins" <john_at_hpe.ufl.edu> wrote:

>
>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.

I ran the following test on 7.1, 7.2, 7.3 and 8.0 getting the same result on all 4 after doing an insert into pre and an update on pre. My results were:

PRE-TABLE: S SEQNO LOCATION DOB S MEDREC RACE - ---------- ---------- --------- - ---------- ---------- X 262 location 30-SEP-97 x medrec race  

POST-TABLE:        SEQNO LOCATION DOB S MEDREC RACE POSTING_D

---------- ---------- --------- - ---------- ---------- ---------
                                                        30-SEP-97
       262 location   30-SEP-97 x medrec     race       30-SEP-97
 

One thing to note in your code however is that when you insert, you are using

:old.column.  When inserting, there are no old values (you probably mean to use
:new.column in the insert and :old.column in the update).  All of the
:old.columns are NULL in the insert action.  If any of your columns were defined

as NOT NULL in the POST table, that could explain the error.

create table pre(
status char(1),
seqno number default userenv('sessionid'), location varchar2(10) default 'location',

dob         date         default sysdate,
sex         char(1)      default 'x',
medrec      varchar2(10) default 'medrec',
race        varchar2(10) default 'race'

);  

create table post(

seqno       number       ,
location    varchar2(10) ,
dob         date         ,
sex         char(1)      ,
medrec      varchar2(10) ,
race        varchar2(10) ,

posting_dt date
);    

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;
/

insert into pre (status) values ( 'X' ); update pre set status = 'X';
select * from pre;
select * from post;

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD

http://govt.us.oracle.com/ -- downloadable utilities

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Sep 30 1997 - 00:00:00 CDT