Re: Mutating table solution ???

A copy of this was sent to Clifford Buetikofer <clifford_buetikofer_at_merck.com> (if that email address didn't require changing) On Tue, 03 Aug 1999 11:33:39 -0400, you wrote:

>I'm trying to check a record insertion to see if a particular date
>
>column is null. If it's null, I want to insert the sysdate if the
>
>sysdate is greater than 01-AUG-1999.
>

your code below, when executed after 01-aug-1999 would update the origdate field of EVERY row in the table -- i don't think that is what you want..

if you want to

• review every record being inserted AND
  • in the event the origdate column is NULL
    • make it be SYSDATE

just code simply:

create or replace trigger my_trigger
before insert on myTable
for each row
begin

• i would leave off the "and sysdate > ..." part since its always
• true from now on anyway.. if ( :new.origdate is null and sysdate >= to_date('01-AUG-1999') ) then :new.origdate := sysdate; end if; end;

>
>
>I tried using the mutating table solution in the Oracle Press PL/SQL
>
>book on page 259. The book advises creating a package which contains a
>
>PL/SQL table of the inserted data. Then a a row-level trigger runs
>
>which stores the data in the PL/SQL table. Then a statement_level
>
>trigger unloads the PL/SQL table and checks whether to update the date
>
>column with the sysdate or not.
>
>
>
>I basically have one script which creates the package and then creates
>
>the 2 triggers. However I keep getting a compiler error:
>
>
>
>PLS-00103:Encountered the symbol "CREATE"
>
>
>
>Listed below is the PL code...
>
>At my wits end,
>
>Cliff
>
>{----------------CUT HERE--------------------}
>
>
>
>package datefix AS
>
>type t_origdate is table of cab.origdate%type
>
> index by binary_integer;
>
>
>
> v_origdate t_origdate;
>
> v_numentries BINARY_INTEGER :=0;
>
>end datefix;
>
>
>
>CREATE OR REPLACE trigger date1
>
>AFTER INSERT ON cab
>
>for each row
>
>begin
>
> datefix.v_numentries := datefix.v_numentries + 1;
>
> datefix.v_origdate(datefix.v_numentries):= :new.origdate;
>
>end date1;
>
>
>
>CREATE OR REPLACE trigger date2
>
>after insert on cab
>
>DECLARE
>
> v_loopindex number;
>
> v_neworigdate date;
>
>BEGIN
>
>for v_loopindex in 1..datefix.v_numentries LOOP
>
> v_neworigdate := datefix.v_origdate(v_loopindex);
>
>
>
> IF v_neworigdate is null THEN
>
> update CAB
>
> set origdate = sysdate
>
> where to_char(sysdate,'DD-MON-YYYY') >= '01-AUG-1999';
>
> END IF;
>
>
>
> end loop;
>
> datefix.v_numentries := 0;
>
>END date2;
>
>
> The contents of this message express only the sender's opinion.
> This message does not necessarily reflect the policy or views of
> my employer, Merck & Co., Inc. All responsibility for the statements
> made in this Usenet posting resides solely and completely with the
> sender.

-- 
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
 
Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation