MYSQL 记录修改触发器例子

create table test.db_log
(
  id             int auto_increment,
  APP            varchar(10)                                         null,
  TYPE           enum ('INSERT', 'UPDATE', 'DELETE', 'WHITE', 'LOG') null,
  HOST           varchar(64)                                         null,
  USER           varchar(32)                                         null,
  TIME           int(7)                                              null,
  MARK           varchar(200)                                        null,
  creat_datetime datetime default CURRENT_TIMESTAMP                  null,
  constraint db_log_id_uindex
  unique (id)
);

create index db_log_APP_HOST_TYPE_index
  on test.db_log (APP, HOST, TYPE);

alter table test.db_log
  add primary key (id);


DROP TRIGGER IF EXISTS job_depend_insert_trigger;
CREATE TRIGGER job_depend_insert_trigger
  BEFORE INSERT
  ON job_depend
  FOR EACH ROW
  BEGIN
    INSERT INTO test.db_log (APP, HOST, USER, TIME, TYPE, MARK)
    SELECT '', HOST, USER, TIME, 'INSERT', concat_ws(',', NEW.job_id)
    FROM information_schema.PROCESSLIST
    WHERE HOST NOT IN (SELECT HOST FROM db_log WHERE TYPE = 'WHITE')
      AND info like '%job_depend_insert_trigger%'
      AND NEW.app IN (SELECT DISTINCT APP FROM db_log WHERE TYPE = 'LOG');
  END;

DROP TRIGGER IF EXISTS job_depend_update_trigger;
CREATE TRIGGER job_depend_update_trigger
  BEFORE UPDATE
  ON job_depend
  FOR EACH ROW
  BEGIN
    INSERT INTO test.db_log (APP, HOST, USER, TIME, TYPE, MARK)
    SELECT '', HOST, USER, TIME, 'UPDATE', concat_ws(',', OLD.job_id, NEW.job_id)
    FROM information_schema.PROCESSLIST
    WHERE HOST NOT IN (SELECT HOST FROM db_log WHERE TYPE = 'WHITE')
      AND info like '%job_depend_update_trigger%'
      AND (OLD.app IN (SELECT DISTINCT APP FROM db_log WHERE TYPE = 'LOG')
             OR NEW.app IN (SELECT DISTINCT APP FROM db_log WHERE TYPE = 'LOG'));
  END;

DROP TRIGGER IF EXISTS job_depend_delete_trigger;
CREATE TRIGGER job_depend_delete_trigger
  BEFORE DELETE
  ON job_depend
  FOR EACH ROW
  BEGIN
    INSERT INTO test.db_log (APP, HOST, USER, TIME, TYPE, MARK)
    SELECT '', HOST, USER, TIME, 'DELETE', concat_ws(',', OLD.job_id)
    FROM information_schema.PROCESSLIST
    WHERE HOST NOT IN (SELECT HOST FROM db_log WHERE TYPE = 'WHITE')
      AND info like '%job_depend_delete_trigger%'
      AND OLD.app IN (SELECT DISTINCT APP FROM db_log WHERE TYPE = 'LOG');
  END;


SELECT *
FROM information_schema.PROCESSLIST;