Re: Help for an Oracle Newbie

From: Shakespeare <>
Date: Wed, 20 Aug 2008 09:26:51 +0200
Message-ID: <48abc73b$0$192$>

"DA Morgan" <> schreef in bericht

> tcole6 wrote:
>> On Aug 19, 1:42 pm, tcole6 <> wrote:

>>> On Aug 19, 1:34 pm, tcole6 <> wrote:
>>>> I posted awhile back and got a ton of help trying to convert an
>>>> application I wrote using SQL Server ported over to Oracle. Now I was
>>>> hoping to get some additional assistance.
>>>> Obviously there are several built in data types in SQL Server that do
>>>> not exist in Oracle. Of course, I've managed to make use of most of
>>>> them, so I have some additional work to do...I was hoping someone
>>>> could review and tell me if I'm no the right track:
>>>> uniqueidentifier - in SQL Server this generates a system wide unique
>>>> identifier. That's not exactly what I need, I just need a database
>>>> wide unique identifier. So what I did was to create a Sequence, named
>>>> NEWID, that starts at 1, intervals at 1 and has the max upper limit.
>>>> Then for each table I created a column named ID with data type number
>>>> and size 20. I then created a trigger in each table:
>>>> select NEWID.nextval into:new.ID from dual;
>>>> end;
>>>> Of course I had to use a different name for the trigger under each
>>>> table, but the rest is the same.
>>>> So question 1: Does this look like a feasible solution and is there an
>>>> easier way than to have to create a separate trigger for each table.
>>>> In SQL I could have created one trigger and assigned it to the various
>>>> tables. I did not see a way to do this ni Oracle. (yuch!)
>>>> timestamp - In SQL Server this automatically updates with the time of
>>>> the transaction for any insert or update. No client work required.
>>>> What I did here was to create a column named LASTMODIFIED with data
>>>> type DATE. Then I created trigger to udpate this:
>>>> Date ndate = new Date();
>>>> select ndate into:new.LASTMODIFIED from dual;
>>>> end;
>>>> I selected all columns (except lastmodified) for the udpate.
>>>> Question 2: Does this look feasible or is there an easier way to
>>>> accomplish this task?
>>>> The last was not a datatype problem, but rather a default value
>>>> assignment problem. In SQL Server I always create a CreatedDate column
>>>> with type Date and default value getdate(). This automatically assigns
>>>> the current date anytime a record is first created. I tried to create
>>>> a function, but had no luck. So I also used a trigger to set the
>>>> createddate.
>>>> Question 3: Is a function a better way to do this and if so can I
>>>> assign a function as the default value for a column? if so maybe I
>>>> could get some help with that?
>>>> Thanks again for your time.
>>> To answer my own Question 3 (I think), I found CURRENT_DATE. I assume
>>> I can just set this as the default to get what I need.- Hide quoted
>>> text -
>>> - Show quoted text -
>> Okay.... answers to 2 and 3...
>> I created a sigle trigger like so:
>> begin
>> if inserting then
>> :new.CREATEDDATE := sysdate;
>> end if;
>> :new.LASTMODIFIED := sysdate;
>> end;
>> Which sets the CREATEDDATE if it's a create, and sets the LASTMODIFIED
>> anytime columns are updated.
> It appears that your trigger is going to put the exact same value
> into two separate columns accomplishing nothing of value.

It doesn't, only when inserting. Which could simply be solved by uing 'else' in stead of 'end if'.


> For inserts just define your column as follows:
> createddate  DATE DEFAULT SYSDATE,
> lastmodified DATE DEFAULT SYSDATE,
> Then you trigger should be a BEFORE UPDATE and only act
> when LASTMODIFIED is null.

Which would never be the case because of the default value of lastmodified (sysdate).
Default values save a trigger firing on insert though.


> -- 
> Daniel A. Morgan
> Oracle Ace Director & Instructor
> University of Washington
> (replace x with u to respond)
> Puget Sound Oracle Users Group
Received on Wed Aug 20 2008 - 02:26:51 CDT

Original text of this message