Since SQL does not have a FOR-EACH
statement, how could we verify if there is a difference on each value from the OLD
object to the NEW
object in a AFTER UPDATE
type TRIGGER
without knowing the table columns [and table names]?
Example today:
CREATE TRIGGER `audit_events_ugly`
AFTER UPDATE ON `accounts`
FOR EACH ROW
BEGIN
DECLARE changes VARCHAR(8000);
IF OLD.user_name <> NEW.user_name THEN
SET changes = 'user_name from % to %';
END IF;
IF OLD.user_type <> NEW.user_type THEN
SET changes = CONCAT(changes, ', user_type from % to %');
END IF;
IF OLD.user_email <> NEW.user_email THEN
SET changes = CONCAT(changes, ', user_email from % to %');
END IF;
CALL reg_event(how_canI_get_tableName?, @user_id, changes);
-- and that can go on and on... differently for every table.
END;
Example as I wish it could be:
CREATE TRIGGER `audit_events_nice`
AFTER UPDATE ON `accounts`
FOR EACH ROW
BEGIN
DECLARE changes VARCHAR(8000);
DECLARE N INT DEFAULT 1;
FOREACH OLD, NEW as OldValue, NewValue
BEGIN
IF OldValue <> NewValue THEN
SET changes = CONCAT(changes, ', column N: % to %');
SET N = N + 1;
END IF;
CALL reg_event(how_canI_get_tableName?, @user_id, changes);
-- now I can paste this code in every table that is audited..
END;
Any Ideas? WHILE, FOREACH, ARRAYS...
I think you cannot do that directly in a for-loop at the trigger level.
However, you could use a script to generate the trigger code. You would need to re-generate it every time you add/remove a field to the table (usually not frequently).