How to update PL/SQL field when user enters data through MS Access. 1

[golyg]

I have a user who uses ms access to update/insert data thats in an oracle db. When the user enters a new record into a certain table they want a date to automatically be populated into a date field.

How can I do this? would I use a view?

thanks,

 

[SantaMufasa]

Golly,

There are several methods by which you could populate the date field, either on the front end (MS-Access)or the back end (Oracle). I'm certain that MS-Access has a function similar to Oracle's SYSDATE function that provides the current Time/Date of the db server. Likewise, on the (Oracle) back end, you can have an Oracle "INSERT" trigger that populates your date field with SYSDATE.

Mufasa
(aka Dave of Sandy, Utah, USA @ 15:34 (07May04) UTC (aka "GMT" and "Zulu"), 08:34 (07May04) Mountain Time)

 

[golyg]

Thanks for the quick reply...
I'd like to keep it on the backend, so does that mean If I set a trigger in PL/SQL for when the user inserts data through MS Access?

thanks again,

 

[SantaMufasa]

Golly,

Here, then, is a sample of the code for your need. (The "INSERT" statement represents the behaviour of your MS-Access front-end application):

SQL> create table golyg (create_date date, other_data varchar2(30))
  2  /

Table created.

SQL> create or replace trigger timestamp_golyg
  2   before insert on golyg for each row
  3  begin
  4   :new.create_date := sysdate;
  5  end;
  6  /

Trigger created.

SQL> insert into golyg (other_data) values ('This is a test')
  2  /

1 row created.

SQL> commit;

Commit complete.

SQL> select to_char(create_date,'yyyy-mm-dd hh24:mi:ss') Created, other_data from golyg
  2  /

CREATED             OTHER_DATA
------------------- ------------------------------
2004-05-07 09:12:58 This is a test

1 row selected.

Let me know if you have additional questions.

Mufasa
(aka Dave of Sandy, Utah, USA @ 16:13 (07May04) UTC (aka "GMT" and "Zulu"), 09:13 (07May04) Mountain Time)

 

[sem]

Another (less restrictive) way is to create column with DEFAULT SYSDATE. In this case this column will be populated by current date only in case no other value is provided.

Regards, Dima

http://seminihin.narod.ru

 

[golyg]

Thanks, SantaMufasa this worked to the "T"
perfect....

Sem, great hint,