Re: pulling sysdate out of a table with a trigger (newbie)

sidney_buck_at_hotmail.com (Sidney Buck) wrote in message news:<e8c6761d.0112190633.23f7a635_at_posting.google.com>...
> I am building a system that will track events and tasks. I would like
> to store the data for each event in a table, so that the trigger can
> look up events in the table that meet certain criteria and then insert
> records in the task/event table with the appropriate event status,
> date, etc.
>
> My problem is that with each event I would like to store a date that
> is either sysdate or sysdate plus some number of days. Right now I am
> storing this date as 'sysdate' or 'sysdate + 60' in a varchar2 field.
> But the trigger is not happy when it tries to insert this into the
> task table, where the date is formatted as a date.
>
> Can anyone tell me how to get the trigger to recognize 'sysdate' as a
> true sysdate?
>
> Thank you,
> Sidney

hi sidney

SQL> select * from t1;

CC DECP
------ -------------

1      sysdate + 30
2      sysdate + 60
3      sysdate + 60

4 rows selected.

SQL> select * from t2;

CC DECP DATE1
------ --------- ---------

1      a
1      b
1      c
2      c
2      d

5 rows selected.

suppose let the event table be t1 with date values stored as strings into the field DECP
and let t2 be the task table with DATE1 be the date filed which we have to update or insert
then if you use the following logic in your trigger then the problem described by you can be solved .

  1 declare
  2 cursor c1 is select * from t1;
  3 begin
  4 for crec in c1 loop
  5 if instr(crec.decp,'+')=0 then
  6 update t2
  7 set date1=sysdate
  8 where t2.cc=crec.cc;
  9 else
 10 update t2
 11 set date1=sysdate+substr(crec.decp,(instr(crec.decp,'+')+1))  12 where t2.cc=crec.cc;
 13 end if;
 14 end loop;
 15* end;

SQL> _at_grpsqa

PL/SQL procedure successfully completed.

after executing the abvoe procedure we can get the following output

SQL> select * from t2;

CC DECP DATE1

------ --------- ---------
1      a         19-JAN-02
1      b         19-JAN-02
1      c         19-JAN-02
2      c         18-FEB-02
2      d         18-FEB-02

5 rows selected.

two additional functions you need to use are instr() and substr() oracle will automatically convert from charcter into number if the charcters are numbers i.e if the cahrcters are of the form '12' so on.

regards
raghu
Certified DBA Received on Wed Dec 19 2001 - 21:35:19 CET