kinanson的技術回憶

CREATE trigger tri_mininib  on mininib for insert,update,delete --事件，這裡表示新增修改刪除都會觸發
              (自定義名稱)       (資料表)      

as

declare @compid   nvarchar(10) --定義變數，nvarchar一定要給大小，否則預設都只有一碼

declare cur_deleted cursor local for
                      select compid,
                             hid,
                             isnull(matid,''),
                             isnull(qty,0),
                             isnull(uniprice,0),
                             isnull(amt,0),
                             bodyno,
                             spec,
                             codecol
                      from deleted  --把刪除後的值抓出來

open cur_deleted
fetch next from cur_deleted into @compid,@hid,@matid,@qty,@uniprice,@amt,@bodyno,@spec,@codecol  --把刪除後的值塞進變數，變數我沒寫出來，請自行定義
while(@@fetch_status=0) --迴圈的部份一定有begin和end
begin

update mpuimportc set qty = qty - @qty
where imhid=@hid and imcompid=@compid  and imbodyno = @bodyno and imprgid='in'
and compid=@compid and stkid = @stkid 

fetch next from cur_deleted into @compid,@hid,@matid,@qty,@uniprice,@amt
      ,@bodyno,@spec,@codecol --把上面那段直接複製下來，沒這段會造成無窮迴圈

end

close cur_deleted
deallocate cur_deleted

Declare cur_inserted Cursor Local for
                     select compid,
                             hid,
                             isnull(matid,''),
                             isnull(qty,0),
                             isnull(uniprice,0),
                             isnull(amt,0),
                             bodyno,
                             spec,
                             codecol
                     from inserted  --抓到新增後的值

Open cur_inserted
Fetch next from cur_inserted into @compid,@hid,@matid,@qty,@uniprice,@amt,@bodyno,@spec,@codecol  --把新增後的值塞進變數
WHILE (@@FETCH_STATUS = 0)
BEGIN

update mpuimportc set qty = qty + @qty
where imhid=@hid and imcompid=@compid and imbodyno = @bodyno and imprgid='in'
and compid=@compid and stkid = @stkid

Fetch next from cur_inserted into @compid,@hid,@matid,@qty,@uniprice,@amt
,@bodyno,@spec,@codecol

end

Close cur_inserted
Deallocate cur_inserted