Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Oracle-SQL Problem

Re: Oracle-SQL Problem

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 08 Sep 1998 10:27:26 GMT
Message-ID: <360305c4.15577859@192.86.155.100>


A copy of this was sent to mansegak_at_my-dejanews.com (if that email address didn't require changing) On Tue, 08 Sep 1998 06:49:48 GMT, you wrote:

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

It can be done with a trigger and some defaults. the trigger is needed to support the updates.

Note that if we use defaults, the programmer can override them. It is perhaps 'safer' to use a trigger to do the insert processing as well as the programmer cannot override the trigger at runtime -- only the defaults. This will prevent someone from doing "insert into mytable( creation_date, created_by ) values ( sysdate-10, 'Someone Else' );" ... The example as coded does not take this precaution -- you would need another trigger that looks like the update one but changes the other fields..

create table mytable ( creation_date date default sysdate,

                       created_by    varchar2(30) default user,
                       last_updated_date date default sysdate,
                       last_updated_by   varchar2(30) default user );

create or replace trigger mytable_trigger before update on mytable
foreach row
begin

   :new.last_update_date := sysdate;
   :new.last_updated_by := user;
end;
/

>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
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

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



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

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Tue Sep 08 1998 - 05:27:26 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US