Re: Help for an Oracle Newbie

From: DA Morgan <damorgan_at_psoug.org>
Date: Thu, 21 Aug 2008 13:56:29 -0700
Message-ID: <1219358851.810796@bubbleator.drizzle.com>

Shakespeare wrote:

> "DA Morgan" <damorgan_at_psoug.org> schreef in bericht 
> news:1219171786.192311_at_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.
> 
> 
> 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_at_x.washington.edu (replace x with u to respond)
>> Puget Sound Oracle Users Group
>> www.psoug.org 

Did you try what I suggested or make a decision based on an assumption?

CREATE TABLE t (
rid INTEGER,
createddate DATE DEFAULT SYSDATE,
lastmodified DATE DEFAULT SYSDATE);

CREATE OR REPLACE TRIGGER bu_t
BEFORE UPDATE
ON t
FOR EACH ROW
BEGIN
   IF (:NEW.lastmodified IS NULL)
   OR (:NEW.lastmodified = :OLD.lastmodified) THEN      :NEW.lastmodified := SYSDATE;
   END IF;
END bu_t;
/

INSERT INTO t (rid) VALUES (1);

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS'; SELECT * FROM t;

UPDATE t
SET rid = 2;

SELECT * FROM t;

Works perfectly on my server.

-- 
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.org

Received on Thu Aug 21 2008 - 15:56:29 CDT