触发器中如何先判断结果集为空再进行插入操作

[crazy_fishegg]

代码:

CREATE tigger 'tri_article_update' BEFORE UPDATE ON 't_article' FOR EACH ROW
BEGIN
      IF (select COUNT(*) from t_article where cover=new.cover and content=new.content)=0
	THEN INSERT into t_file (url,content) VALUES (new.cover,new.content)
END

描述:在更新t_article之前,先查询一下t_article表中,当前的cover和content字段的值有没有改变,如果有改变的话,先把就得值用t_file表记下来再进行update操作

结果报错,说IF语句已经出错了,求教...

[benluobo]

delimiter $$ CREATE trigger tri_article_update before UPDATE ON t_article FOR EACH ROW BEGIN declare cnt int; select COUNT(*) into cnt from t_article where cover=new.cover and content=new.content; if(cnt=0) then INSERT into t_file (url,content) VALUES (new.cover,new.content); end if; END$$

[卖水果的net]

-- 用 先取出 count（*） 值   , new 前面要加冒号
dwcount int ; 
select  count(*) into dwcount  from t_article 
where cover=:new.cover and content=:new.content ; 
IF dwcount = 0 then 
    INSERT into t_file (url,content) VALUES (new.cover,new.content) ; 
end if ; 

[卖水果的net]

引用 1 楼 wmxcn2000 的回复:

-- 用 先取出 count（*） 值   , new 前面要加冒号
dwcount int ; 
select  count(*) into dwcount  from t_article 
where cover=:new.cover and content=:new.content ; 
IF dwcount = 0 then 
    INSERT into t_file (url,content) VALUES (new.cover,new.content) ; 
end if ; 

dwcount int ; 
select  count(*) into dwcount  from t_article 
where cover=:new.cover and content=:new.content ; 
IF dwcount = 0 then 
    INSERT into t_file (url,content) VALUES (:new.cover,:new.content) ; 
end if ; 

[卖水果的net]

错了，错了，刚才按 oracle 的语法写的。 换成 mysql 的。

INSERT into t_file (url,content) VALUES (new.cover,new.content)
where not exists(select * from t_article where cover=new.cover and content=new.content)
    

[crazy_fishegg]

引用 3 楼 wmxcn2000 的回复:

错了，错了，刚才按 oracle 的语法写的。 换成 mysql 的。

INSERT into t_file (url,content) VALUES (new.cover,new.content)
where not exists(select * from t_article where cover=new.cover and content=new.content)
    

最终代码写成这样

CREATE TRIGGER 'tri_article_update' BEFORE UPDATE ON 't_article' 
FOR EACH ROW
BEGIN
INSERT into t_file (url,content) VALUES (new.cover,new.content)
where NOT EXISTS(select * from t_article where cover=new.cover and content=new.content)
END

但是还是报错啊~

[rick-he]

空是null值,不是0,if要用is null CREATE tigger 'tri_article_update' BEFORE UPDATE ON 't_article' FOR EACH ROW BEGIN IF (select COUNT(*) from t_article where cover=new.cover and content=new.content) =0 (改为is null) THEN INSERT into t_file (url,content) VALUES (new.cover,new.content) END