Re: Help for an Oracle Newbie
From: DA Morgan <damorgan_at_psoug.org>
Date: Tue, 19 Aug 2008 11:49:55 -0700
Message-ID: <1219171786.192311@bubbleator.drizzle.com>
>> 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: Tue, 19 Aug 2008 11:49:55 -0700
Message-ID: <1219171786.192311@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.
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.
-- 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 Tue Aug 19 2008 - 13:49:55 CDT