Re: Oracle-SQL Problem

Use the functions INSERTING and UPDATING in a database trigger on MYTABLE: Example:
 .......
begin

--
-- beginning of audit information section
--
if ( inserting )
 then
   --
   -- Give values to CREATED_BY and CREATION_DATE if not yet present
   --
   if (( created_by is null ) OR ( creation_date is null ))
    then
     created_by := user;
     creation_date := sysdate;
   end if;
 elsif ( updating )
 then
   --
   -- Give values to MODIFIED_BY and MODIFICATION_DATE
   --
   last_updated_by := user;
   last_updated_date := sysdate;
end if;
end;
--
-- end of audit information section
--
.....

With regards,

Alex


mansegak_at_my-dejanews.com schreef:



> In Oracle Application,how can I implement this?

>

> 1)I created my own table say MYTABLE

> 2)This table contain few field such as CREATION_DATE,

>   CREATED_BY,LAST_UPDATED_DATE, LAST_UPDATED_BY

>   etc...etc....(some other fields that necessary)

>

> My question is, how I want to get the "default value to be

> insert" into for this 4 special field everytime the user do

> a DML (Data Manipulation :INSERT/UPDATE) "automatically", ie:

>

> a)CREATION_DATE - Maybe I can default it to SYSDATE (am I correct?)

> b)But how about the rest (CREATED_BY,LAST_UPDATE_DATE & LAST_UPDATE_BY)

>

> I mean, how the system will know it is an update operation (since, if so

> I can use SYSDATE as well for LAST_UPDATE_DATE) in SQL.(How can I implement

> it using SQL statement?)

>

> Can anybody help me on this since I really "blur" on this situation.

>

> Thanks.

> Norazman


>

> -----== Posted via Deja News, The Leader in Internet Discussion ==-----

> http://www.dejanews.com/rg_mkgrp.xp   Create Your Own Free Member Forum