Re: Help for an Oracle Newbie

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. Received on Tue Aug 19 2008 - 13:09:41 CDT