我的倉庫系統有如下兩個表：入庫表storein (sn_no varchar(6) not null, in_no varchar(8) not null, price numeric not null, mater_no varchar(10) not null, quantity numeric, ) 庫存表 create table store (mater_no varchar(8) not null, quantity numeric, price numeric) go 我想在入庫表有數據加入時用觸發器自動在庫存表中加上同類材料的庫存數，以平均價（同類總的庫存材料價值除以庫存數）作為新的庫存中的單價（price);如果庫存表中無此類材料，則加入這一筆資料。由於小弟初學sql，不知道如何寫這個觸發器，特請教各位先進，非常感謝！

您好!    假設說您是利用資料感知元件來新增入庫表的資料, 對應的元件分別為DBEdit1(材料編號mater_no); DBEdit2(單價price); DBEdit3(Quantity數量)...請在Form中加入三個ADOQuery資料集元件, ADOQuery1對應入庫表, ADOQuery2和ADOQuery3對應庫存表, ADOQuery2用作查詢, ADOQuery3用作編輯, 試試將如下程式碼寫在ADOQuery1(入庫表)的AfterPost事件中:

begin
  ADOQuery2.Close;
  ADOQuery2.Add.Clear;
  ADOQuery2.SQL.Add('Select * From 庫存表 Where ');
  ADOQuery2.SQL.Add('mater_no = ''' DBEdit1.Text ''' ');
  ADOQuery2.Open;
  if ADOQuery2.RecordCount <> 0 then
    begin
      ADOQuery3.Close;
      ADOQuery3.SQL.Clear;
      ADOQuery3.SQL.Add('Update 庫存表 Set ');
      ADOQuery3.SQL.Add('Price = (Price*Quantity   ');
      ADOQuery3.SQL.Add('''' DBEdit2.Text '''* ');
      ADOQuery3.SQL.Add(''' DBEdit3.Text ''')/ ');
      ADOQuery3.SQL.Add('(Quantity   ''' DBEdit3.Text ''') ');
      ADOQuery3.SQL.Add('Where mater_no = ''' DBEdit1.Text ''''); 
      ADOQuery3.ExecSQL;
      ADOQuery3.SQL.Clear;
      ADOQuery3.SQL.Add('Update 庫存表 Set ');
      ADOQuery3.SQL.Add('Quantity = Quantity   ');
      ADOQuery3.SQL.Add('''' DBEdit3.Text ''' Where ');
      ADOQuery3.SQL.Add('mater_no = ''' DBEdit1.Text'''');
      ADOQuery3.ExecSQL;
    end
  else
    begin
      ADOQuery3.Close;
      ADOQuery3.SQL.Clear;
      ADOQuery3.SQL.Add('Insert Into 庫存表 Values ');
      ADOQuery3.SQL.Add('(''' DBEdit1.Text ''', ');
      ADOQuery3.SQL.Add('''' DBEdit3.Text ''', ');
      ADOQuery3.SQL.Add('''' DBEdit2.Text ''')');
      ADOQuery3.ExecSQL;
    end;
  ADOQuery2.Close;
  ADOQuery2.SQL.Clear;
  ADOQuery2.SQL.Add('Select * From 庫存表');
  ADOQuery2.Open;
  DBEdit1.Clear;
  DBEdit2.Clear;
  DBEdit3.Clear;
end;

以上寫法僅供參考! 試試看! ===================== 努力,相信會獲得美麗! 忻晟 發表人 - cashxin2002 於 2003/09/08 17:09:18

------
忻晟

謝謝cashxin2002，我就試試。 但我還想知道能不能通過使用觸發器的方式來實現上述功能。因為這可能還涉及到修改和刪除入庫表中記錄的情況，如果都這樣使用afterpost會不會有較大的困擾呢？

請問你的資料庫種類為何, 另外, 一次會入庫的資料會是多筆或是單筆呢?

假設是用 MSSQL 若是用 SYBASE , 則 : 1. scroll cursor -> cursor 2. deallocate -> deallocate cursor 3. @@fetch_status -> @@sqlstatus

create trigger t_storein on storein for INSERT,UPDATE,DELETE as
begin
  declare  @master_no varchar(8),
           @price        numeric,
           @quantity     numeric      declare cur_insstorein scroll cursor 
       for select master_no,price,quantity from inserted
  declare cur_delstorein scroll cursor 
       for select master_no,price,quantity from deleted    -- Insert or Update(New Record)
  open cur_insstorein
  fetch cur_insstorein into @master_no,@price,@quantity
  while (@@fetch_status=0)
    begin
      if (select count(*) from store where master_no=@master_no)=0
        insert store (master_no,price,quantity) values (@master_no,@price,@quantity)
      else
        update store set price=((price*quantity @price*@quantity)/(quantity @quantity)),quantity=quantity @quantity
          from store where master_no = @master_no
      fetch cur_insstorein into @master_no,@price,@quantity
    end    -- Delete or Update(Old Record)
  open cur_delstorein
  fetch cur_delstorein into @master_no,@price,@quantity
  while (@@fetch_status=0)
    begin
      ...自己嚐試寫寫...
      fetch cur_delstorein into @master_no,@price,@quantity
    end      close cur_insstorein
  close cur_delstorein
  deallocate cur_insstorein
  deallocate cur_delstorein
  return
end
go

發表人 - Mickey 於 2003/09/09 10:32:50

不好意思，想再次請教一下：為什麼觸發器創建好後在執行時卻出現錯誤－'Cursor is not open.'?

我在 >'

Mickey,您好！我照你說的都改過數據表及觸發器，並能創建好觸發器，但就是執行時彈出錯誤信息：－'Cursor is not open'因此數據不能存入數據庫。現附上兩個表的SQL語句，請再幫我看看。不勝感激！ create table storein --入庫表 (sn_no varchar(6) not null, in_no varchar(8) not null, pro_name varchar(12) not null, price numeric not null, mater_no varchar(12) not null, in_date datetime not null, recieve varchar(8), quantity numeric, type varchar(10), note varchar(50) ) go create table store 庫存表 (mater_no varchar(12) not null, quantity numeric not null, price numeric not null) go 觸發器 create trigger t_storein on storein for INSERT,UPDATE,DELETE as begin declare @mater_no varchar(12), @price numeric, @quantity numeric declare cur_insstorein scroll cursor for select mater_no,price,quantity from inserted declare cur_delstorein scroll cursor for select mater_no,price,quantity from deleted -- Insert or Update(New Record) open cur_insstorein fetch cur_insstorein into @mater_no,@price,@quantity while (@@fetch_status=0) begin if (select count(*) from store where mater_no=@mater_no)=0 insert store (mater_no,price,quantity) values (@mater_no,@price,@quantity) else update store set price=((price*quantity @price*@quantity)/(quantity @quantity)),quantity=quantity @quantity from store where mater_no = @mater_no fetch cur_insstorein into @mater_no,@price,@quantity end -- Delete or Update(Old Record) /* open cur_delstorein fetch cur_delstorein into @master_no,@price,@quantity while (@@fetch_status=0) begin ...自己嚐試寫寫... fetch cur_delstorein into @master_no,@price,@quantity end*/ --因為尚未完全弄懂，所以這部分我還沒有自己寫。 close cur_insstorein close cur_delstorein deallocate cur_insstorein deallocate cur_delstorein return end

/* open cur_delstorein fetch cur_delstorein into @master_no,@price,@quantity while (@@fetch_status=0) begin ...自己嚐試寫寫... fetch cur_delstorein into @master_no,@price,@quantity end*/ 因為你把 'open cur_delstorein' remark 掉, 而後面的 close/deallocate 仍留著...所以報錯. 改從下一行做 remark : open cur_delstorein /* fetch cur_delstorein into @master_no,@price,@quantity while (@@fetch_status=0) begin ...自己嚐試寫寫... fetch cur_delstorein into @master_no,@price,@quantity end*/

非常感謝Mickey,現在問題解決了！還是自己水平太差了，給您增添了很多麻煩，我定當努力！

這個 Trigger 的觸發時機是 create trigger t_storein on storein for INSERT,UPDATE,DELETE as... 所以 Update 時也會觸發, Update 時暫存檔 (inserted/deleted) 分別存著 新/舊 值,所以還是可以 處理到 "store" Table