CODEWITHSUNDEEP
mysqlsqltriggers
user3066838
user3066838

Reputation: 11

How to write a DYNAMIC mysql History-Trigger

I have the following problem:

I want to create a history - but DYNAMIC

So i have an existing history-Trigger like: how to write mysql trigger

But i am searching for an solution like:

INSERT INTO History VALUES ( NOW(), 'action', NEW.* )

If the rows are sorted as in the main table - it shouldnt matter how i insert them? Or do they?

I have no idea if this solution is working - so before i use it in commerce - i wanna be sure if it is useful - or if I should update the Trigger if I 'alter' the Table Structure.

THANKS FIRST, for your answer - because i can't answer the question - because i am newbie - I edit my first statement:

And what about that:

.... INSERT INTO History VALUES (SELECT NOW(), 'Insert', new.* FROM new) ....

Upvotes: 1

Views: 1394

Answers (2)

aschoerk
aschoerk

Reputation: 3593

I might have a solution for this and would be interested in your opinion. The original intention was to avoid the redundancy stemming of repeating the select lists in the insert statements and the necessity to recreate the trigger when the tables get changed. This can possibly be avoided if some preliminaries are met:

The history table should have its additional attributes at the beginning. The rest of the history table must be designed as the original. Additionally you must have a primary key in the original you can use for a query. Then:

create table A (`id` bigint(20) AUTO_INCREMENT , a varchar(100),  PRIMARY KEY (`id`))

create table AH (flag bit(1), user varchar(100), `id` bigint(20) , a varchar(100))

The additional fields are flag and user.

Then the trigger:

DELIMITER $$
CREATE TRIGGER `T` AFTER INSERT 
ON `A` FOR EACH ROW BEGIN 
    INSERT INTO AH select 1, user(), mn.* from A mn where id = NEW.id;
END
$$
DELIMITER ;

seems to do what is intended.

even after: alter table A add column (c varchar(200))

alter table AH add column (c varchar(200))

the trigger works unchanged.

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562280

Short answer:

A better option is to use an auditing plugin for MySQL. Then you don't have to write triggers at all.

Wildcard syntax for elements in a VALUES clause like you show is not supported (as you know).

And what about that:

.... INSERT INTO History VALUES (SELECT NOW(), 'Insert', new.* FROM new) ....

No.

Not only does this syntax fail to work (you can't select from NEW), but you'd need extra columns in the History table anyway.

It might be possible to design the trigger to query the set of columns from INFORMATION_SCHEMA.COLUMNS and use GROUP_CONCAT to put together a comma-separated list of them for inclusion in a dynamic SQL INSERT statement.

But I would never put that into a production system, because querying the I_S.COLUMNS has pretty high overhead, and you won't be happy with the performance cost if your triggers are doing that every time you insert, update, or delete tables.

I have consulted for a site that couldn't scale because of this issue. They ran a large number of stored procedures with dynamic SQL, using I_S to verify every table name and column name. Their database server was pegged on CPU load as a result, and it was a big problem for their continued growth.

I would design the trigger to hard-code the column names. If you alter the table, you would have to redesign the trigger.

Consider how frequently you alter the table compared to how frequently the trigger executes. It's probably on the order of 1:10^9.

Upvotes: 1

Related Questions