How can I get the name of the table for any given trigger in MySQL?

Question

I'm using Mysql 5.7 and I want to use triggers to set up some simple audit trail logging. The table name the entry comes from is relevant so we'd like to capture it. I know I can hard code the name here but I was hoping for something a little more dynamic and portable (copy and paste to another table). Is it possible to get the table name that a given trigger is associated while inside of the trigger?

Here is the guts of my trigger...

BEGIN
SET @table_name = `i_want_the_table_name_for_this_trigger`;

INSERT INTO `test`.`audit_table1` (`serialNumber`, `auditTrailTableName`, `action`, `communicationDate`) VALUES (OLD.id,  @table_name, NEW.type, NEW.createdOn);

END

I found this answer for SQL Server... How to get table_name in a trigger - SQL Server

Any help is appreciated.

you can get the table name dynamically from information_schema through the name of the trigger. Unfortunately, you have to hard code the trigger name into the code, so this does not really help. — Shadow
– Shadow, Commented Oct 7, 2022 at 22:30
ms sql server and mysql vastly differ from each other. Just because you can do a thing in one, it does not mean you can do it in the other. — Shadow
– Shadow, Commented Oct 7, 2022 at 23:08
Thank you @Shadow for your response. I only meant that reference as an example of more precisely what I was hoping to be able to do. — kasdega
– kasdega, Commented Oct 7, 2022 at 23:23

O. Jones · Accepted Answer · 2022-10-07 22:25:51Z

3

You Can't Do That™.

Triggers need hard coded table names.

answered Oct 7, 2022 at 22:25

O. Jones

109k17 gold badges134 silver badges187 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How can I get the name of the table for any given trigger in MySQL?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related