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? asked 08 Mar '19, 10:02 Volker Barth |
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. answered 11 Mar '19, 12:17 Volker Barth |