请教下 DB2 触发器

蒋猫

请教下   DB2 触发器

比如语句为

IF 1=1 then

update tab1 set col1=col+1

else

update tab1 set col1=col+1

end  老报错 帮助里也没IF语句的介绍  还有在触发器里 可以定义变量吗？就向SQL declare @a int?

faceDragon

触发器可以用compound sql，就应该可以定义变量。

你报的什么错？是不是update语句后没写;的缘故啊

蒋猫

请问下  能否给我范例呀 既有IF语句的还有变量的  我查了帮助和资料
发觉资料里的触发器的例子跟帮助的例子 是一样的

orablue

1，关于IF的例子：
CREATE PROCEDURE UPDATE_SALARY_IF (IN employee_number CHAR(6), INOUT rating SMALLINT) LANGUAGE SQL BEGIN DECLARE not_found CONDITION FOR SQLSTATE ’02000’; DECLARE EXIT HANDLER FOR not_found SET rating = -1; IF rating = 1 THEN UPDATE employee SET salary = salary * 1.10, bonus = 1000 WHERE empno = employee_number; ELSEIF rating = 2 THEN UPDATE employee SET salary = salary * 1.05, bonus = 500 WHERE empno = employee_number; ELSE UPDATE employee SET salary = salary * 1.03, bonus = 0 WHERE empno = employee_number; END IF; END Related reference:


2，触发器的例子：
CREATE TRIGGER NEW_HIRED AFTER INSERT ON EMPLOYEE FOR EACH ROW UPDATE COMPANY_STATS SET NBEMP = NBEMP + 1


CREATE TRIGGER REORDER AFTER UPDATE OF ON_HAND, MAX_STOCKED ON PARTS REFERENCING NEW AS N FOR EACH ROW WHEN (N.ON_HAND < 0.10 * N.MAX_STOCKED) BEGIN ATOMIC VALUES(ISSUE_SHIP_REQUEST(N.MAX_STOCKED - N.ON_HAND, N.PARTNO)); END

蒋猫

非常谢谢

蒋猫

原帖由 "orablue" 发表：
1，关于IF的例子：
CREATE PROCEDURE UPDATE_SALARY_IF (IN employee_number CHAR(6), INOUT rating SMALLINT) LANGUAGE SQL BEGIN DECLARE not_found CONDITION FOR SQLSTATE ’02000’; DECLARE EXIT HANDLER FOR..........

不好意思 你给我的好象是过程的例子，请问下有触发器含IF语句的例子吗
还有 是否在触发器中 不好定义变量呀

蒋猫

//sql中
CREATE TRIGGER tri_sp_crk_del ON [dbo].[JL_CRKMC]
FOR DELETE
AS
declare @jlbh int                --记录编号
declare @lx int                        --类型       
declare @ckdm char(2)                --仓库代码
declare @spbh char(20)
declare @sl decimal(9, 2)
declare @je decimal(15,2)

select @jlbh = jlbh, @lx = lx, @spbh = spbh, @sl = sl, @je = je from DELETED
select @ckdm = ckdm from jl_crk where jlbh = @jlbh and lx = @lx

if @lx = 0 --入库
begin
        if  Exists(select 1 from kc_je  where kc_je.spbh = @spbh and kc_je.ckdm = @ckdm)
                update kc_je set sl = sl - @sl  , je = je - @je      where spbh = @spbh and ckdm = @ckdm
        else
                insert into kc_je  (ckdm, spbh, sl, je)
                              values  (@ckdm, @spbh, -1 * @sl, -1* @je)
end
else if @lx = 1 --出库
begin
        if  Exists(select 1 from kc_je  where kc_je.spbh = @spbh and kc_je.ckdm = @ckdm)
                update kc_je set sl = sl + @sl  , je = je + @je      where spbh = @spbh and ckdm = @ckdm
        else
                insert into kc_je  (ckdm, spbh, sl, je)
                              values  (@ckdm, @spbh, @sl, @je)
end


^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^我想转DB2：

///  请帮我看下以下这段该怎么写呀
when (old_jl_mc.lx=0 )
begin
         if  Exists(select 1 from kc_je  where kc_je.spbh = old_jl_crkmc.spbh and kc_je.ckdm =(select ckdm from jl_crk where jlbh = old_jl_crkmc.jlbh and lx = old_jl_crkmc.lx)) then
          update kc_je set sl = sl - old_jl_crkmc.sl  , je = je - old_jl_crkmc.je where spbh = old_jl_crkmc.spbh and ckdm =(select ckdm from jl_crk where jlbh = old_jl_crkmc.jlbh and lx = old_jl_crkmc.lx);
         else
            insert into kc_je  (ckdm, spbh, sl, je)
          values  ((select ckdm from jl_crk where jlbh = old_jl_crkmc.jlbh and lx = old_jl_crkmc.lx) , old_jl_crkmc.spbh, -1 * old_jl_crkmc.sl, -1* old_jl_crkmc.je);
      end if
end
when (old_jl_mc.lx)=1
begin
     if Exists(select 1 from kc_je  where kc_je.spbh = old_jl_crkmc.spbh and kc_je.ckdm =(select ckdm from jl_crk where jlbh = old_jl_crkmc.jlbh and lx = old_jl_crkmc.lx)) then
          update kc_je set sl = sl + old_jl_crkmc.sl  , je = je + old_jl_crkmc.je where spbh = old_jl_crkmc.spbh and ckdm =(select ckdm from jl_crk where jlbh = old_jl_crkmc.jlbh and lx = old_jl_crkmc.lx);
        else
           insert into kc_je  (ckdm, spbh, sl, je)
          values  ((select ckdm from jl_crk where jlbh = old_jl_crkmc.jlbh and lx = old_jl_crkmc.lx) , old_jl_crkmc.spbh, 1 * old_jl_crkmc.sl, 1* old_jl_crkmc.je);
      end if
end

orablue

CREATE TRIGGER tri_sp_crk_del
After DELETE On JL_CRKMC For Each Row
Begin ATOMIC
When (old_jl_mc.lx=0 )
begin
  When (select Count(1) from kc_je  where kc_je.spbh = old_jl_crkmc.spbh and kc_je.ckdm =(select ckdm from jl_crk where jlbh = old_jl_crkmc.jlbh and lx = old_jl_crkmc.lx))>;0  
    update kc_je set sl = sl - old_jl_crkmc.sl  , je = je - old_jl_crkmc.je
    where spbh = old_jl_crkmc.spbh and ckdm =(select ckdm from jl_crk where jlbh = old_jl_crkmc.jlbh and lx = old_jl_crkmc.lx);
  When (select Count(1) from kc_je  where kc_je.spbh = old_jl_crkmc.spbh and kc_je.ckdm =(select ckdm from jl_crk where jlbh = old_jl_crkmc.jlbh and lx = old_jl_crkmc.lx))=0  
    insert into kc_je  (ckdm, spbh, sl, je)
    values  ((select ckdm from jl_crk where jlbh = old_jl_crkmc.jlbh and lx = old_jl_crkmc.lx) ,
    old_jl_crkmc.spbh, -1 * old_jl_crkmc.sl, -1* old_jl_crkmc.je);
End
When (old_jl_mc.lx)=1
begin
   When (select Count(1) from kc_je  where kc_je.spbh = old_jl_crkmc.spbh and kc_je.ckdm =(select ckdm from jl_crk where jlbh = old_jl_crkmc.jlbh and lx = old_jl_crkmc.lx))>;0
      update kc_je set sl = sl + old_jl_crkmc.sl  , je = je + old_jl_crkmc.je where spbh = old_jl_crkmc.spbh and ckdm =(select ckdm from jl_crk where jlbh = old_jl_crkmc.jlbh and lx = old_jl_crkmc.lx);
   When (select Count(1) from kc_je  where kc_je.spbh = old_jl_crkmc.spbh and kc_je.ckdm =(select ckdm from jl_crk where jlbh = old_jl_crkmc.jlbh and lx = old_jl_crkmc.lx))=0
      insert into kc_je  (ckdm, spbh, sl, je)
      values  ((select ckdm from jl_crk where jlbh = old_jl_crkmc.jlbh and lx = old_jl_crkmc.lx) ,
      old_jl_crkmc.spbh, 1 * old_jl_crkmc.sl, 1* old_jl_crkmc.je);
End


不知道这样行不行？

蒋猫

不好使呀 用一个WHEN  是好的
两个嵌套就报错
有谁知道 具体是什么格式呀

orablue

报什么错误？