Mysql中的触发器定义与使用

create table user_logs(
    opera_text varchar(1000) comment '插入的内容',
    opera_time datetime comment '插入的时间',
    opera varchar(20) comment '操作的类型，insert/delete/update'
 );
create trigger tb_user_insert_tigger
    after insert on sp for each row
    begin
        insert into user_logs(opera_text, opera_time, opera)
        values(concat('插入的名字=',new.name,'，插入的年龄=',new.age),now(),'insert');
    end;
show triggers;
insert into sp(age, name) values (100,'大魔王');

create trigger tb_user_update_trigger
    after update on sp for each row
    begin
        insert into user_logs(opera_text, opera_time, opera) values
        (concat('更新前的名字=',old.name,' 更新前的年龄=',old.age,' 更新后的名字=',new.name,' 更新后的年龄=',new.age),now(),'update');
    end;
show triggers;
update sp set age=1000 where name='李四';

create trigger tb_user_delete_trigger
    after delete on sp for each row
    begin
        insert into user_logs(opera_text, opera_time, opera)
            values(concat('删除的名字=',old.name,' 删除前的年龄=',old.age),now(),'delete');
    end;
show triggers;
delete from sp where name='李四';