Re: Help for an Oracle Newbie
From: Shakespeare <whatsin_at_xs4all.nl>
Date: Wed, 20 Aug 2008 09:26:51 +0200
Message-ID: <48abc73b$0$192$e4fe514c@news.xs4all.nl>
>>> On Aug 19, 1:34 pm, tcole6 <tco..._at_gmail.com> 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:
>>>> CREATE TRIGGER "LINXAS"."GET_ID" BEFORE
>>>> INSERT ON "LINXAS"."WF_HEADER" FOR EACH ROW begin
>>>> 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:
>>>> CREATE TRIGGER "LINXAS"."GET_LASTMODIFIED" BEFORE
>>>> INSERT
>>>> OR UPDATE OF "COMPLETE", "CREATEDDATE", "ID", "MODIFIEDBY" ON
>>>> "LINXAS"."WF_HEADER" FOR EACH ROW begin
>>>> 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 -
Date: Wed, 20 Aug 2008 09:26:51 +0200
Message-ID: <48abc73b$0$192$e4fe514c@news.xs4all.nl>
"DA Morgan" <damorgan_at_psoug.org> schreef in bericht news:1219171786.192311_at_bubbleator.drizzle.com...
> tcole6 wrote: >> On Aug 19, 1:42 pm, tcole6 <tco..._at_gmail.com> wrote:
>>> On Aug 19, 1:34 pm, tcole6 <tco..._at_gmail.com> 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:
>>>> CREATE TRIGGER "LINXAS"."GET_ID" BEFORE
>>>> INSERT ON "LINXAS"."WF_HEADER" FOR EACH ROW begin
>>>> 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:
>>>> CREATE TRIGGER "LINXAS"."GET_LASTMODIFIED" BEFORE
>>>> INSERT
>>>> OR UPDATE OF "COMPLETE", "CREATEDDATE", "ID", "MODIFIEDBY" ON
>>>> "LINXAS"."WF_HEADER" FOR EACH ROW begin
>>>> 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'.
Shakespeare
> > For inserts just define your column as follows: > > CREATE TABLE ... ( > 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.
Shakespeare
> -- > Daniel A. Morgan > Oracle Ace Director & Instructor > University of Washington > damorgan_at_x.washington.edu (replace x with u to respond) > Puget Sound Oracle Users Group > www.psoug.orgReceived on Wed Aug 20 2008 - 02:26:51 CDT