Re: Help for an Oracle Newbie


"DA Morgan" <damorgan@xxxxxxxxx> schreef in bericht
news:1219171786.192311@xxxxxxxxxxxxxxxxxxxxxxxxx
tcole6 wrote:
On Aug 19, 1:42 pm, tcole6 <tco...@xxxxxxxxx> wrote:
On Aug 19, 1:34 pm, tcole6 <tco...@xxxxxxxxx> 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@xxxxxxxxxxxxxxxx (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org


.

Relevant Pages

  • Re: Help for an Oracle Newbie
    ... application I wrote using SQL Server ported over to Oracle. ... Obviously there are several built in data types in SQL Server that do ... Of course I had to use a different name for the trigger under each ... In SQL Server I always create a CreatedDate column ...
    (comp.databases.oracle.server)
  • Re: Help for an Oracle Newbie
    ... application I wrote using SQL Server ported over to Oracle. ... easier way than to have to create a separate trigger for each table. ... In SQL Server I always create a CreatedDate column ...
    (comp.databases.oracle.server)
  • Re: Help for an Oracle Newbie
    ... application I wrote using SQL Server ported over to Oracle. ... Obviously there are several built in data types in SQL Server that do ... Of course I had to use a different name for the trigger under each ... In SQL Server I always create a CreatedDate column ...
    (comp.databases.oracle.server)
  • Re: Help for an Oracle Newbie
    ... application I wrote using SQL Server ported over to Oracle. ... Obviously there are several built in data types in SQL Server that do ... Of course I had to use a different name for the trigger under each ...
    (comp.databases.oracle.server)
  • Help for an Oracle Newbie
    ... application I wrote using SQL Server ported over to Oracle. ... Obviously there are several built in data types in SQL Server that do ... Of course I had to use a different name for the trigger under each ...
    (comp.databases.oracle.server)