Help for an Oracle Newbie

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