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, 10:02

Volker%20Barth's gravatar image

Volker Barth
33.8k330486716
accept rate: 33%


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.

permanent link

answered 11 Mar, 12:17

Volker%20Barth's gravatar image

Volker Barth
33.8k330486716
accept rate: 33%

edited 12 Mar, 05:42

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×243
×7

question asked: 08 Mar, 10:02

question was seen: 438 times

last updated: 12 Mar, 05:42