MySQL Bugs: #97189: Trigger after SELECT not possible, no alternatives

Bug #97189	Trigger after SELECT not possible, no alternatives
Submitted:	10 Oct 2019 18:16	Modified:	11 Oct 2019 13:15
Reporter:	Danny Holstein	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	All	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	trigger select

Description:
I understand a "trigger after SELECT" isn't a feature. My attempts at a workaround can't work because of Error Code: 1442, "table already used by...".

- My specific need is for dumb label printer software with ODBC interface connection to MySQL, I want to access a view of label data that have not yet been printed and record details related to that access. In my case, specifically so that we may print once, and only once.

- For controlled information, including secret information, this feature would allow us to more simply log access to it. Using a combination of triggers, it'd be a simple task to see if we the access to the DB were abnormal.

How to repeat:
I have two tables, `FCC_info` (the data I want to control) and `labelprints` (the audit data). I constructed a VIEW of the data in `FCC_info` that has not been printed, one of the columns is a stored function intended to update `labelprints` with a timestamp. Of course, I run into Error Code: 1442, I can't INSERT into `labelprints` since I'm using it in the VIEW.

Suggested fix:
I propose adding it to the feature request queue.

Thank you for the bug report. Duplicate of https://bugs.mysql.com/bug.php?id=47008 which clarify is a documented restriction.

I don't see how that is related to my issue. We have a TRIGGER ON INSERT and UPDATE, I'm asking for a TRIGGER ON SELECT for the reasons I list. Your link is unrelated, please explain how the two are related.

Hello Mr. Holstein,

Thank you for your bug report.

However, we can not accept feature that would activate a TRIGGER before or after SELECT. The reason is that it would go against valid SQL standards.

You could, however, try to design some new, standards compliant, feature that would accomplish what you desire.

Not a bug.

I understand. In theory, I can get inside the source and add the feature, too.

But I believe firmly it should be part of the SQL standard, how do I propose adding it to the standard? Who do I lobby?

SQL Standards Committee is not US Congress. No lobbying is allowed ...