Re: Help for an Oracle Newbie

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. Received on Tue Aug 19 2008 - 12:42:29 CDT