mysql触发器使用

-- ruoyi_base.system_dictdata definition

CREATE TABLE `test_info` (
  `id` int NOT NULL AUTO_INCREMENT,
  `dept_belong_id` varchar(256) DEFAULT NULL,
  `update_datetime` int ,
  PRIMARY KEY (`id`)
  
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


drop table ruoyi_base.test_info_log;

CREATE TABLE `test_info_log` (
  `id` int NOT NULL ,
  `log_txt` varchar(256) DEFAULT NULL 
  
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


select * from ruoyi_base.test_info;

select * from  test_info_log;

insert into ruoyi_base.test_info (id,dept_belong_id,update_datetime) values (3,"王五",100);



drop trigger tig_test_info;
 

-- 触发器创建
create trigger tig_test_info
    before update  on test_info
    for each row
    begin
	    if new.update_datetime <> old.update_datetime or 
	    new.dept_belong_id <> old.dept_belong_id
	    then 
     insert into  test_info_log(id,log_txt) values(new.id,concat(new.dept_belong_id,new.update_datetime));
    end if;
    end ;
  


 update ruoyi_base.test_info set update_datetime = update_datetime + 0,dept_belong_id = '张三1'
where id=1;

truncate table ruoyi_base.test_info_log;

select * from  ruoyi_base.test_info_log;