这个触发器如何写？

[cqliutao]

此触发器为SQL SERVER如何改为ORACLE
CREATE TRIGGER ti_input_base ON bj_input
FOR INSERT AS
declare @li_rows integer
declare @lm_sl number(16,4)
declare @lm_jinge number(16,4)
declare @ls_ckno varchar2(10)
declare @ls_code varchar2(20)
declare @li_id integer
BEGIN
/*----------------如果库存表中存在,更新库存--------------------------*/

declare cur_input cursor for select code,sl,jinge,ck_no ,id from inserted
select @li_rows = count(*) from inserted
open cur_input
while @li_rows > 0
begin
select @li_rows = @li_rows - 1
fetch cur_input into @ls_code,@lm_sl,@lm_jinge,@ls_ckno ,@li_id

if exists(select code from bj_base where code=@ls_code and ck_no=@ls_ckno)
begin
/*----------------如果库存表中存在,更改库存-----------------*/

update bj_base
set amount_kc=amount_kc + @lm_sl,jinge=jinge+@lm_jinge,kl_date=getdate()
where code = @ls_code and ck_no=@ls_ckno
if @@error <> 0 or @@rowcount <> 1
begin
raiserror 20001 '更新库存出错!'
close cur_input
deallocate cur_input
return
end
end
/*----------------如果库存表中不存在,插入新库存--------------------------*/
else
begin
insert into bj_base(code,amount_kc,sl,jinge,ck_no,tempfield,kl_date,id)
values(@ls_code,@lm_sl,@lm_sl,@lm_jinge,@ls_ckno,'',getdate(),@li_id)

if @@error <> 0 or @@rowcount <> 1
begin
raiserror 20001 '更新库存出错!'
close cur_input
deallocate cur_input
return
end
end

end
close cur_input
deallocate cur_input

/*-----------写进出总帐-------------------------*/
insert into total_table (bj_id,code, op_date, note,
num, now_kc, kc_jinge,
in_out, fs_date, ck_no ,price )
select a.id, a.code, getdate(), '[入库]',
a.sl, b.amount_kc, b.jinge,
1, getdate(), a.ck_no,a.price
from inserted a,bj_base b
where a.id = b.id and a.ck_no=b.ck_no
if @@error <> 0
begin
raiserror 20001 '写进出总帐出错!'
close cur_input
deallocate cur_input
return
end



END

[beckhambobo]

估不到oracle与ms sql触发器有如此区别:

CREATE TRIGGER ti_input_base
before insert ON bj_input
for each row
BEGIN
--如果库存表中存在,更改库存
update bj_base
set amount_kc=amount_kc + :new.lm_sl,jinge=jinge+:new.lm_jinge,kl_date=sysdate
where code = :new.ls_code and ck_no=:new.ls_ckno;
--如果库存表中不存在,插入新库存
if sql%notfound then
insert into bj_base(code,amount_kc,sl,jinge,ck_no,tempfield,kl_date,id)
values(:new.ls_code,:new.lm_sl,:new.lm_sl,:new.lm_jinge,:new.ls_ckno,null,sysdate,:new.li_id);
--写进出总帐
insert into total_table (bj_id,code, op_date, note,
num, now_kc, kc_jinge,
in_out, fs_date, ck_no ,price )
select :new..id, :new.code, sysdate, '[入库]',
:new..sl, b.amount_kc, b.jinge,
1, sysdate, :new.ck_no,new.price
from bj_base b
where :new.id = b.id and :new.ck_no=b.ck_no;

END;

[beckhambobo]

sorry,在--写进出总帐前加一句:end if;

[lok9828]

gz