Re: Help for an Oracle Newbie
Date: Fri, 22 Aug 2008 10:23:48 +0200
Message-ID: <48ae7796$0$187$e4fe514c@news.xs4all.nl>
"DA Morgan" <damorgan_at_psoug.org> schreef in bericht
news:1219358851.810796_at_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?
>> createddate DATE DEFAULT SYSDATE,
> CREATE TABLE t (
> rid INTEGER,
> 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
> Did you try what I suggested or make a decision based on an
> assumption
YES I did try! And it did NOT work the way you suggested first.
I DID try what you suggested:
<quote>
Then you trigger should be a BEFORE UPDATE and only act
when LASTMODIFIED is null.
<unquote>
> 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
^
Hey, you didn't suggest this line!
And
> IF (:NEW.lastmodified IS NULL)
- is never true because of the default value so a useless condition (except for avoiding comparing null with null maybe) and
- is incorrect, because lastmodified should be updated when not null too.
Now you come with a solution where you initially assign lastmodified with
the same default value as createddate,
of which you stated
<quote again>
>>> It appears that your trigger is going to put the exact same value
>>> into two separate columns accomplishing nothing of value.
<unquote again>
Which wasn't even true, as I said here:
>> It doesn't, only when inserting.
Now what you accomplished is putting default values in stead of an insert
trigger, which gives the user/programmer a chance to insert incorrect values
in the audit columns with an insert statement
AND an update statement.
And even worse, if he does, the update trigger won't fire because
:NEW.lastmodified <> :OLD.lastmodified
And worse than all: I think an update trigger without any conditions, just :NEW.lastmodified := SYSDATE;
would suffice.
The point I wanted to make here is that this newbie came with a perfectly working, though suboptimal solution (did YOU try that one?), which you replaced by a suggestion for a non-working "better" solution and then try to talk your way out of it by adding new conditions in your trigger where, to my opinion, a simple solution would have been enough.
Shakespeare Received on Fri Aug 22 2008 - 03:23:48 CDT