Re: Oracle-SQL Problem

From: TurkBear <johng_at_mm.com>
Date: Fri, 11 Sep 1998 17:01:46 GMT
Message-ID: <35f95692.11688086@news2.mm.com>

edawad_at_my-dejanews.com wrote:

>In article <6t2k2c$cq0$1_at_nnrp1.dejanews.com>,
> mansegak_at_my-dejanews.com 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)
>>
>> 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
>>
>Use database triggers.
>
>-----== Posted via Deja News, The Leader in Internet Discussion ==-----
>http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum

For instance, here is a trigger I use to audit actions affecting one of my tables - you can use it for ideas...

-------------------------------------- AFTER UPDATE/INSERT/DELETE Trigger -

Declare
oname varchar2(100);
unam varchar2(100);
usid number;
Begin
select user into unam from global_name; select max(sid) into usid from v$mystat; select osuser into oname from v$session where username = unam and

       sid = usid;
if inserting then
insert into audit_lid values('Insert',sysdate,oname,:new.lid,'N/A') ; elsif updating then
insert into audit_lid values('Update',sysdate,oname,:new.lid,:old.lid); elsif deleting then
insert into audit_lid values('Delete',sysdate,oname,:old.lid,'N/A'); else
raise_application_error(-20100,'Undefined Action...'); end if;
end;

---

Hope it helps...

John Greco
Oracle DBA ( Netware/7.3) and Application Developer Email to : john.greco_at_dot.state.mn.us

To reply please remove the 'nospam' part of the address Received on Fri Sep 11 1998 - 12:01:46 CDT