The point of database-level audits, such as can be done with triggers is to capture "who" made the change... but from the database's perspective, there is only one "who" -- the MySQL user the application uses. (Also, your devops and DBA users, but primarily, it sounds like the application's changes you're interested in.)
I would suggest that the appropriate approach is for the application to log the app-user actions through deliberate insertions into "history" or "event" tables, referencing that Perl
$userid, which presumably would also be the primary key in "user" table in the database... you'd be generating these logs from application code.
The role of trigger-based audits, then, is primarily one of sanity-checking the application: the audit shows this user's balance increased... did the application write a matching log entry in the appropriate "history" table? The history table should then have the app-user id, and everything cross-references, if the app is correctly logging its actions.
(My personal preference is for the application to modify the database only through calls to stored procedures... in this way, the application can't fail to write these app-user-level history logs, because the stored procedures encapsulate every DML action into a single atomic database call... but I digress.)
workhackaround available in MySQL, if you want to try to hybridize these two approaches... even if it's only to satisfy your curiosity and broaden your understanding of the nature of what you're dealing with.
User Defined Variables in MySQL are untyped variables, much like Perl scalars: they are whatever you put in them, strings, datetimes, floats, or blobs, and they're scoped to the individual MySQL connection. Their value is independent of database transactions, but never visible from one connection to another.
$dbh->do(q[ SET @app_user_id = ? ], undef, $userid);
Until you disconnect from this connection, the variable
@app_user_id (which I just made up, it's just a variable name, but the
@ designates it as a user defined variable) will contain the value you stored there unless you set it to something else. Because these variables have session scope, they also available to triggers, procedures, and stored functions (but not views or events)... so instead of using
CURRENT_USER() in a trigger, use
This, of course, requires that your application initialize this variable each time it begins running code that could update the database, and you have to hold on to that DBI handle, if you're working in a framework that can swap them out from under you.
So, not a perfect solution, but possibly the closest thing you'll find to accomplish what you want, using the setup you want to try.