最近在工作中要动态的创建表和相应的触发器,特此记录下。
1.使用SQL创建触发器
DELIMITER $$
CREATE TRIGGER `catefiles_trigger` AFTER INSERT ON `catefiles` FOR EACH ROW
begin
declare num1 int;
set num1 = (select num from est_client_catescan_status where
cateid=new.cateId and taskid=new.taskId and clientid=new.clientId);
if(num1>=0) then
update catescan_status set num=num1+1 where cateid=new.cateId and taskid=new.taskId and clientid=new.clientId;
else
insert catescan_status(cateid,num,status,taskid,clientid) values(new.cateId,1,0,new.taskid,new.clientId);
end if;
end$$
2.在java程序里创建触发器
String sql=+" CREATE TRIGGER catefiles_trigger AFTER INSERT ON catefiles FOR EACH ROW"