Is there a way to use the OS user as CURRENT resp. LAST USER value with integrated logins?

[VolkerBarth]

When using integrated logins, is there an automatic way to use the connection_property('OSUser') as value for a CURRENT USER or LAST USER special values?

In other words: When I want to log the OS user as the user who has inserted or updated data, do I need a trigger to do so?

[VolkerBarth · Accepted solution]

As alternative to DEFAULT CURRENT USER you can simply use a DEFAULT clause - that's because SQL Anywhere allows DEFAULT clauses with built-in functions when called with constant arguments:

create table T_Test(
   ...
   CreatedByOSUser varchar(128) not null default connection_property('OSUser'),
   ChangedByOSUser varchar(128) not null default connection_property('OSUser'),
   ...);

However, whereas the DEFAULT LAST USER is set automatically by the database engine on UPDATEs, for the OSUser you need to code a before update trigger, such as

create trigger TUB_T_Test
   before update on T_Test
   referencing new as T_N
   for each row
begin
   if update(ChangedByOSUser) then
      -- NOOP: Accept the specified value for ChangedByOSUser
      -- when specified in the SET clause 
   else
      set T_N.ChangedByOSUser = connection_property('OSUser');
   end if;
end;

Note that this works exactly like DEFAULT LAST USER: DEFAULT LAST USER is not automatically updated when you supply an explicit value for the according column within the SET clause of an UPDATE statement. The same is true for this trigger: If you specify a value for the column in the SET clause, it will be accepted, otherwise, the connection property is used.