Re: Hi all
From: pal <jayadevpaleri_at_gmail.com>
Date: Sat, 22 Aug 2009 09:16:08 -0700 (PDT)
Message-ID: <af6d5989-3320-4568-b1e7-1f52bee4a87d_at_s15g2000yqs.googlegroups.com>
On Aug 22, 1:44 pm, Tim X <t..._at_nospam.dev.null> wrote:
> Palooka <nob..._at_nowhere.com> writes:
> > On 21/08/09 18:26, pal wrote:
> >> On Aug 18, 12:17 pm, Tim X<t..._at_nospam.dev.null> wrote:
> >>> pal<jayadevpal..._at_gmail.com> writes:
> >>>> Can any one tell me how to declare a temporary table in a trigger(like
> >>>> in SQL Server)?
>
> >>> OMG! Do NOT do it!
>
> >>> 1. Avoid triggers unless there is a very good reason and no other
> >>> solution will work. Triggers can easily get unmanageable and are
> >>> effectively side effects that can make maintenance and debugging a
> >>> nightmare (especially if different teams end up doing the maintenance).
>
> >>> 2. Creating tables is a DDL (Data Definition Language) statement and
> >>> therefore causes an implicit commit. This would cause unexpected
> >>> behavior with transaction management etc (actually, I don't think you
> >>> can have DDL in triggers for this reason).
>
> >>> 3. Maybe consider a temporary global table which is created already and
> >>> take advantage of its support for transaction independence etc. While
> >>> its not clear why you want to create a table in a trigger, it is likely
> >>> that the features of a temporary global table would provide what you are
> >>> after - though I'm totally guessing of course as we have no details on
> >>> what your trying to do).
>
> >>> Tim
>
> >>> --
> >>> tcross (at) rapttech dot com dot au
>
> >> Thanks for the comments. I am quiet happy to see these comments. I am
> >> getting some orders for my project which i have developed using SQL
> >> Server.But some clients need Oracle version. So I am just trying to
> >> spend some time on Oracle, which is new to me. Once again thanks for
> >> the advice.
>
> > Copy the business requirements.
> > Copy the logical design.
> > Avoid copying or attempting to translate either the physical design or the
> > application coding.
>
> I would also add that if your attempting to port from MS SQL to Oracle,
> you *must* at the very least read the Oracle concepts guide. This will
> at least give you an idea of where things differ, where they are
> similar, but possibly with different terminology and finally, where
> there are features that may actually make things easier, more
> straight-forward or even superior compared to MS SQL.
>
> If you simply attempt to translate the DDL and hoe it all works, you
> will almost certainly come unstuck.
>
> My final suggestion is that rather than ask how to use a temporary table
> in a trigger, ask if one of the concepts you have read about in the docs
> would be suitable and give an outline of what you are trying to
> do. Also, include the version of Oracle you are using.
>
> HTH
>
> Tim
>
> --
> tcross (at) rapttech dot com dot au
Date: Sat, 22 Aug 2009 09:16:08 -0700 (PDT)
Message-ID: <af6d5989-3320-4568-b1e7-1f52bee4a87d_at_s15g2000yqs.googlegroups.com>
On Aug 22, 1:44 pm, Tim X <t..._at_nospam.dev.null> wrote:
> Palooka <nob..._at_nowhere.com> writes:
> > On 21/08/09 18:26, pal wrote:
> >> On Aug 18, 12:17 pm, Tim X<t..._at_nospam.dev.null> wrote:
> >>> pal<jayadevpal..._at_gmail.com> writes:
> >>>> Can any one tell me how to declare a temporary table in a trigger(like
> >>>> in SQL Server)?
>
> >>> OMG! Do NOT do it!
>
> >>> 1. Avoid triggers unless there is a very good reason and no other
> >>> solution will work. Triggers can easily get unmanageable and are
> >>> effectively side effects that can make maintenance and debugging a
> >>> nightmare (especially if different teams end up doing the maintenance).
>
> >>> 2. Creating tables is a DDL (Data Definition Language) statement and
> >>> therefore causes an implicit commit. This would cause unexpected
> >>> behavior with transaction management etc (actually, I don't think you
> >>> can have DDL in triggers for this reason).
>
> >>> 3. Maybe consider a temporary global table which is created already and
> >>> take advantage of its support for transaction independence etc. While
> >>> its not clear why you want to create a table in a trigger, it is likely
> >>> that the features of a temporary global table would provide what you are
> >>> after - though I'm totally guessing of course as we have no details on
> >>> what your trying to do).
>
> >>> Tim
>
> >>> --
> >>> tcross (at) rapttech dot com dot au
>
> >> Thanks for the comments. I am quiet happy to see these comments. I am
> >> getting some orders for my project which i have developed using SQL
> >> Server.But some clients need Oracle version. So I am just trying to
> >> spend some time on Oracle, which is new to me. Once again thanks for
> >> the advice.
>
> > Copy the business requirements.
> > Copy the logical design.
> > Avoid copying or attempting to translate either the physical design or the
> > application coding.
>
> I would also add that if your attempting to port from MS SQL to Oracle,
> you *must* at the very least read the Oracle concepts guide. This will
> at least give you an idea of where things differ, where they are
> similar, but possibly with different terminology and finally, where
> there are features that may actually make things easier, more
> straight-forward or even superior compared to MS SQL.
>
> If you simply attempt to translate the DDL and hoe it all works, you
> will almost certainly come unstuck.
>
> My final suggestion is that rather than ask how to use a temporary table
> in a trigger, ask if one of the concepts you have read about in the docs
> would be suitable and give an outline of what you are trying to
> do. Also, include the version of Oracle you are using.
>
> HTH
>
> Tim
>
> --
> tcross (at) rapttech dot com dot au
Thanx ... I will do that Received on Sat Aug 22 2009 - 11:16:08 CDT