CODEWITHSUNDEEP
mysqldatabaserdbmsmysql-workbench
Chris
Chris

Reputation: 1146

MySQL Global Triggers for a connection

Is it possible to create a trigger that will listen for any insert or update for one database connection/instance (all schemas)?

Here is my business case:

So to summarize, I am looking to see if it is possible to create a global rule that would make it so I do not need to create a trigger for every table, in every schema.

I haven't much experience with triggers, so if you think of something other than a trigger as the answer for catching these events and updating rows in corresponding tables that would be a fantastic answer as well!!

Upvotes: 1

Views: 1866

Answers (1)

Ike Walker
Ike Walker

Reputation: 65567

You can't do this with a single trigger.

However, if you change the lastChanged column from DATETIME to TIMESTAMP then you can use the DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP feature to set the value automatically on any insert or update.

One tradeoff is that the TIMESTAMP data type can only support values from 1970 to 2038, so you can't use that if you need to store values outside that range.

Since you are looking to make this as simple as possible, and you said all of the tables already have a column named lastChanged, you can consider using information_schema to generate the alter table statements you need, and then execute them all at once.

Something like this:

select concat('alter table ',
   t.table_schema,
   '.',
   t.table_name,
   ' modify column ',
   c.column_name,
   ' timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;') as ddl
into outfile '/tmp/lastChanged.sql'
from information_schema.columns c
  inner join information_schema.tables t 
    on t.table_schema = c.table_schema and t.table_name = c.table_name
where c.table_schema = database()
and c.data_type = 'datetime'
and c.column_name = 'lastChanged'
and t.table_type = 'base table';

\. /tmp/lastChanged.sql

Upvotes: 1

Related Questions