這個觸發器該怎麼寫 |
尚未結案
|
danielldf
一般會員 發表:44 回覆:60 積分:20 註冊:2003-05-12 發送簡訊給我 |
我的倉庫系統有如下兩個表:入庫表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,不知道如何寫這個觸發器,特請教各位先進,非常感謝!
|
cashxin2002
版主 發表:231 回覆:2555 積分:1937 註冊:2003-03-28 發送簡訊給我 |
您好! 假設說您是利用資料感知元件來新增入庫表的資料, 對應的元件分別為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
------
忻晟 |
danielldf
一般會員 發表:44 回覆:60 積分:20 註冊:2003-05-12 發送簡訊給我 |
|
timhuang
尊榮會員 發表:78 回覆:1815 積分:1608 註冊:2002-07-15 發送簡訊給我 |
|
Mickey
版主 發表:77 回覆:1882 積分:1390 註冊:2002-12-11 發送簡訊給我 |
假設是用 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 |
danielldf
一般會員 發表:44 回覆:60 積分:20 註冊:2003-05-12 發送簡訊給我 |
|
Mickey
版主 發表:77 回覆:1882 積分:1390 註冊:2002-12-11 發送簡訊給我 |
|
danielldf
一般會員 發表:44 回覆:60 積分:20 註冊:2003-05-12 發送簡訊給我 |
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
|
Mickey
版主 發表:77 回覆:1882 積分:1390 註冊:2002-12-11 發送簡訊給我 |
/* 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*/
|
danielldf
一般會員 發表:44 回覆:60 積分:20 註冊:2003-05-12 發送簡訊給我 |
|
Mickey
版主 發表:77 回覆:1882 積分:1390 註冊:2002-12-11 發送簡訊給我 |
本站聲明 |
1. 本論壇為無營利行為之開放平台,所有文章都是由網友自行張貼,如牽涉到法律糾紛一切與本站無關。 2. 假如網友發表之內容涉及侵權,而損及您的利益,請立即通知版主刪除。 3. 請勿批評中華民國元首及政府或批評各政黨,是藍是綠本站無權干涉,但這裡不是政治性論壇! |