System function User_Id() is not returning the correct user ID of the current user in a trigger

[chinmaydixit]

Version: SQL Anywhere 16.0.0.2127

Scenario:

1. A .NET app connects to the db with a user 'AA'. ConnectionString has user 'AA'. Connection Pooling: ON Provider: iAnywhere.Data.SQLAnywhere.v4.5.dll

2. The .NET app then switches to another user 'BB'. ConnectionString now has user 'BB'.

3. There are no new connections in the db with user 'AA'. Verified this by calling ClearAllPools method. (https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlconnection.clearallpools?view=d...

4. The .NET app then UPDATEs a record in a table with user 'BB'. This fires the BEFORE UPDATE trigger of the table.

Issue:

A call to function User_Id() inside this trigger is returning the previous user 'AA' even though user 'BB' made the UPDATE. This seems like a SQL Anywhere defect to me.

Query inside the trigger:

SELECT user_name FROM sysuser WHERE user_id = User_Id()

[VolkerBarth]

Just a hint: The name of the current user is available without a query, just use the user_name() builtin function or the "current user" special value...

That being said, you are running a quite old v16 build. There have been several fixes to connection pooling, see here. So a newer build might be worthwhile to try...

[chinmaydixit]

Thanks for your response Volker. I will try CURRENT_USER and USER_NAME() in the trigger.

[VolkerBarth]

Well, I would not expect a different result compared to your query, it's just more efficient and shorter...

What do you need that user name within the trigger? In case you just want to log the user doing the update, the DEFAULT LAST USER can do so without needing a trigger...

[chinmaydixit]

The trigger is used for audit logging (log the user who did the update) and the update is done from the front end .NET app.

[chinmaydixit]

Using USER_NAME() in place of USER_Id() actually worked !!!

USER_NAME() returns the correct user inside the trigger in the scenario where the user id was switched. I am wondering how the implementation would be different within Sybase.

Thank you so much Volker !!! You made my day. You are a rock star 🙂

[VolkerBarth]

Well, I would not expect a different result compared to your query, it's just more efficient and shorter...

Using USER_NAME() in place of USER_Id() actually worked !!!

LOL, I'm glad my expectation was wrong –even if I don't understand why it does make a difference...:)