get tablename on which trigger got fired

[Uwe Sauerbrey]

Hi all,

now I have another question belonging to triggers.
Can I somehow determine which table was involved when a trigger fired?

explanation:
I have written an update-trigger for logging-purposes. For maintenance
reasons I want all tables to have the same trigger code. But for the logging
I need to know which table was involved in the update-process.

TIA
Uwe

[Reg Domaratzki]

You need to write a trigger for a specific table, so "finding" the table
doesn't really make sense.

CREATE TRIGGER bu_customer BEFORE UPDATE ON Customer
REFERENCING OLD as old_row NEW AS new_row FOR EACH ROW
BEGIN
// do specific actions for this table that may involve old and new rows
call sp_Common_Logging ('Customer');
END;

You know within that trigger that you're dealing with the Customer table, so
"finding" the table is pretty trivial, since you know what it is.

Can you be more specific on what you're trying to log and how you want "the
same trigger code" for all triggers? Given that every table will have
different columns with different data types (if they aren't why do you have
more than one table in the database?), and even the primary keys may be
different, it's usually difficult to write common code to handle logging.

--
Reg Domaratzki, Sybase iAnywhere Solutions
Sybase Certified Professional - Sybase ASA Developer Version 8
Please reply only to the newsgroup

iAnywhere Developer Community : http://www.ianywhere.com/developer
ASA Patches and EBFs : http://downloads.sybase.com/swx/sdmain.stm
-> Choose SQL Anywhere Studio
-> Set "Platform Preview" and "Time Frame" to ALL

"Uwe Sauerbrey" <saue...@solution-execute.de> wrote in message
news:EzJ#$cfzCH...@forums.sybase.com...

[Uwe Sauerbrey]

uups,

yes Reg, you're of course right.
I already know at which table I am. So I need to do some copy and paste for
to duplicate my code for all tables.

I already have the code to do the logging. It's a statement level trigger. I
do a lookup on the systable and syscolumn tables to get the list of all
columns and primary-key-columns. Then I loop through the old and new rows
and concatenate all values.

That way I get a concatenated list of the pk-columns, columns, old
pk-values, new pk-values, old values and new values and are able to store
that in another table.

One problem still resists:
When I loop through the old and new rows, then I do this with no ORDER
BY-clause. I can't add an ORDER BY because the primary key values can
change.
Can I likely rely on the order in which the rows from the old-row-table and
the new-rows-table get selected?

At the moment my code assumes, that 2 cursors (one for old-rows, one for
new-rows) are fetching the rows in the same order as the update has been
done.

Thanks for any reply
Uwe

"Reg Domaratzki" <rdomarat_AT_sybase_DOT_com> schrieb im Newsbeitrag
news:m1nOSkf...@forums.sybase.com...