At 05:09 AM 8/03/2008, you wrote:

>i am trying to mark (via a column that reserved for this purpose)
>updated/inserted records in a trigger with name of the current savepoint.
>
>How can i get current savepoint's name?

A user savepoint's "name" is a client-side symbol (identifier) for the argument to an SQL call from the client, in the context of the current transaction. So, while the client can create a savepoint between two other statements and refer back to it by using that symbol, it is unknown to any subsequent code that will execute on the server. Internal savepoints are not available at all.

Assuming that your purpose is to have some string that you can write to the affected records during an AFTER trigger, you could write a stored procedure that you can call from your After Update or Insert triggers to construct a varchar variable according to the information you want to store, and call it at whatever point you need during your write operation, i.e. outside or inside your FOR loop.

For example:

create procedure GetContextID
returns (context_id varchar(92))
as
declare stub varchar(72);
declare context_id varchar(92);
declare time_now varchar(30);
begin
stub = cast (current_connection as varchar(20)) || ':'
|| cast (current_transaction as varchar(20)) || ':'
|| cast (current_user as varchar(20)) || ':' ;

time_now = cast(cast('NOW' as timestamp) as varchar(30));
context_id = stub || time_now;
end

Your triggers would just need to declare a variable for the context id and execute the SP at the appropriate time, e.g.,

create trigger ...
active after insert
as
declare variable vcontext_id varchar(92);
.....
begin
.....
.....
execute procedure GetContextID returning_values(:vcontext_id)

and you would just plug the variable :vcontext_id into your INSERT or UPDATE statement.

A different approach might be to maintain a table for each savepoint created by your application, that stores (without committing) a varchar containing the name of the most recent SAVEPOINT argument along with sufficiently context-unique keys that you can retrieve the savepoint name via a query when the After trigger runs. From V.2.1 onward you could use a transaction-level GTT for this if you didn't need persistence.

./heLen