Re: Hi all

From: Tim X <timx_at_nospam.dev.null>
Date: Sat, 22 Aug 2009 19:44:33 +1000
Message-ID: <873a7k5gji.fsf_at_lion.rapttech.com.au>

Palooka <nobody_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

Received on Sat Aug 22 2009 - 04:44:33 CDT