MYSQL Triggers unable to capture the USER

[Onlyworm15]

Ok. I’ll try to simplify this. So I have a MYSQL database with audit triggers. The problem I am having is that Appsheet sources a mysql database as a specific user. Let’s call this person USER A. This is the problem. Permissions to USER A have been granted already in MYSQL workbench. It allows USER A to view and edit data in mysql workbench. USER A has username and password to access this. That works fine and the permissions are perfect. Now I am trying to test out this process with appsheet. I have created an app and added the cloud database using USER A permissions to the mysql database in the cloud. The problem now is when i share the app, and have the need of 4 other users (e.g. USER B, C, D, E) to edit the data, the audit trigger only notes USER A instead of USER B, C, D, E in the database audit table. Does anyone know of a workaround to reliably track the changes in my database?

[Marc_Dillon]

Maybe this?

[Onlyworm15]

I’ll try that.

[Onlyworm15]

Ok. I’ve tried that and that did not work. it is still noting myself as the editor. Perhaps I should change my trigger in the mysql database? Currently I am using this syntax to extract the user.

DECLARE vUser varchar(50);

– Find username of person performing the INSERT into table
SELECT USER() INTO vUser;

Is there a syntax I should use to grab the login from Appsheet? If yes, can you show me?

[Marc_Dillon]

help.appsheet.com

USEREMAIL()

User's email address

[Steve]

AppSheet is only going to use the database user you supplied when you attached the database to your app. The login credentials users use to login to AppSheet will not be conveyed to the database. You cannot do what you’re trying to do from the database side. Instead, your app itself will need to do it.

Think of it this way: your app users aren’t using the database, they’re using the app. The app is using the database. The database is only aware of the app, not of the users of the app.

[Onlyworm15]

Thanks, Steve. So now I understand, but I guess i am stuck on how to approach this. if I add this field int the app, that would mean I add the audit table to the app. Ugh. don’t know where to go from here. I read USEREMAIL, but stuck on storing this information. Can I not get this user name out of the app and store it?

[Steve]

It would help to know a little more about what you want to accomplish. But, one approach to an audit log would be to create a table in the database to contain the log, a table in the app that writes to that database table, and a set of workflow rules in the app that write to that app table as other app tables receive adds, updates, and deletes. Those workflow rules can include whatever information you want to capture, including the user (as identified by USEREMAIL()) that triggered the log entry.

[Onlyworm15 · Accepted solution]

Steve. I think i figured it out. This is what I did. I added a field to my MySQL Database called “AppSheet_Updated_By”. I then refreshed my tables in Appsheet to make sure the field was there. I then set the default value for that field to USERNAME(). So when I update a record the “Appsheet_Updated_by” field gets updated with the user’s email within my main database table. My database trigger then stores this value in the audit table once it syncs and the trigger goes off.