We have some local temporary tables with connetion specific
information. We use this tables in triggers e.g. to update the record
with the current user. Recently we have some problems with this.
Question: Is it valid to use a local temporary table in a trigger?

Thanks
Arthur

Arthur Hefti schrieb:
I think this will never work. A trigger gets fired when it matches the
conditions you defined, no matter from which connection the action
comes. Local temp tables are visible only for a specific connection. So
if another connection performs an update it wont see the temp tables of
other connections and will therefore fail if you try to reference such a
table. I think you will see table not found errors.

For the current user problem: why don't you use 'last user' as default
value for the field you store your usernames? This automatically updates
it with the user that inserted or updated the row.

Check the documentation for the 'LAST USER' and 'CURRENT USER' special
values!

Tom

Every connection has the same temptable definition. I can't use last
user because the users are not database users. The application logs in
with a default user.

Arthur

at wrote:

Arthur,

how and where exactly are you declaring these temp tables?
E.g. are you using
* CREATE LOCAL TEMPORARY TABLE
* DECLARE LOCAL TEMPORARY TABLE
* CREATE TABLE #tname
* SELECT INTO #tname?

Do you declare these tables in a batch/stored proc in which the
according DML statements are executed so that the triggers are fired in
the scope of the batch/stored proc?

In contrast to Tom, I don't feel that triggers and local temp tables are
generally not fitting (though I strongly support his advice w.r.t. LAST
USER etc. if that is all you need).


However, you should validate that both of the following are true:
a) The table is visible inside the trigger - make sure it is the correct
one as temp tables names have nested scope, i.e. a temp table LT in an
inner block will cover up a temp table LT in an outer block.
b) The table contains the correct contents - this could be validated by
using MESSAGE or UNLOAD statements within the trigger.

Just my 2 cents

Volker


at wrote:

Arthur Hefti wrote:
Would a global temp table (i.e. CREATE GLOBAL TEMPORARY TABLE) be more
adequate here?
It is contained in the system catalog but still each connection can only
access its own data. That might be more robust for use in a trigger.

HTH
Volker

On Thu, 10 Sep 2009 11:34:15 +0200, Arthur Hefti <arthur (AT) catsoft (DOT) ch> wrote:

could you tell some details about these problems?

we regularly use temporary tables from triggers, but we only read data
from those.
we have no problems so far.

btw our scenario is the same as yours: we store connection specific data,
like user,
chosen GUI language, etc. triggers can access this table fine.

For easyness I put all my answers in this post.

We create the tables when the application connects to the database
-> CREATE TABLE #temptable (name long varchar, id integer)
-> Insert connection specific information
We actually do this for about 10 years now. Earlier versions of the
application were using local variables but this was changed for
campatibility with other backends (but now there's no need to support
other databases than SQL Anywhere). This worked without problems.

Now we have second application which can execute some scripts (like
ISQL but without interaction) and writes to tables with triggers as
well.
In this scripts we created the temptable but changed the order of the
columns:
-> CREATE TABLE #temptable ( id integer, name long varchar)

The application that fires the trigger at first loads the definition of
the temptable. When the other application fires the trigger the columns
are mixed up and the statement fails with something like "Cannot
convert 'S' to an int" -> I opened a case for that.

I guess the save solution would be a global temporary table.

Regards
Arthur



Krisztián Pintér wrote:

On Thu, 10 Sep 2009 14:27:33 +0200, Arthur Hefti <arthur (AT) catsoft (DOT) ch> wrote:

aha. we use global temp table.