<リスト1> 入庫テーブルのトリガ
入庫が挿入されたら,発注の入庫済み数に加算する.
Create Trigger 入庫_insert
On dbo.入庫
For Insert
As
If Update (入庫数)
update 発注 set 入庫済み数=入庫済み数+inserted.入庫数
from 発注,inserted
where 発注.発注ID=inserted.発注ID
<リスト2> 入庫テーブルのトリガ
入庫が削除されたなら,発注の入庫済み数を減算する.
Create Trigger 入庫_delete
On dbo.入庫
For Delete
As
update 発注 set 入庫済み数=入庫済み数-deleted.入庫数
from 発注,deleted
where 発注.発注ID=deleted.発注ID
<リスト3> 発注テーブルのトリガ
発注の入庫済み数が更新されたら,部品の在庫数を更新する
Create Trigger 発注_update
On dbo.発注
For Update
As
If Update (入庫済み数)
update 部品
set 在庫数=在庫数-deleted.入庫済み数+inserted.入庫済み数
from 部品,deleted,inserted
where 部品.部品コード=inserted.部品コード
<リスト4> 「sp_delete入庫」ストアドプロシージャ
入力パラメータは削除する入庫の入庫ID
Create Procedure sp_delete入庫
(
@入庫ID integer /* 削除する部品を特定するコード */
)
As
delete 入庫 where 入庫ID=@入庫ID /* 条件を付けて削除 */
<リスト5> 「sp_出庫指示」ストアドプロシージャ
入力パラメータは出庫日
CREATE procedure sp_出庫指示
(
@s_date datetime /* 出庫日を特定する日付 */
)
AS
select 部品.部品名,部品.仕入単価,部品.販売単価,出庫.受注ID,出庫.出庫日,出庫.出庫数
from 部品,受注,出庫
where 部品.部品コード=受注.部品コード
and 受注.受注ID=出庫.受注ID
and 出庫.出庫日 = @s_date
<リスト6> 「sp_在庫予定」ストアドプロシージャ
入力パラメータは,日付指定・部品指定(部品コード)
出力パラメータは,計算結果の在庫数)
CREATE Procedure sp_在庫予定
(
@納期varchar(8), /* 日付 */
@部品コード char(8), /* 部品コード */
@予定在庫数 int output /* 計算され返される在庫値 */
)
As
declare @発注済み数 int, /* 中間的に使う変数 */
@入庫済み数 int,
@入庫予定数 int,
@受注済み数 int,
@出庫済み数 int,
@出庫予定数 int,
@在庫数 int
declare @日付 datetime
set @日付=@納期
/* その日までに入庫予定のある発注の発注数の合計 */
select @発注済み数 =isnull(sum(発注数),0) from 発注 where 部品コード=@部品コード
and 入庫予定日<=@日付
/* すでに入庫している数の合計 */
select @入庫済み数=isnull(sum(入庫済み数),0) from 発注 where
部品コード=@部品コード and 入庫予定日<=@日付
/* これからの入庫予定数 */
set @入庫予定数=@発注済み数-@入庫済み数
/* その日までの受注数の合計 */
select @受注済み数 =isnull(sum(受注数) ,0) from 受注 where 部品コード=@部品コード
and 納期<=@日付
/* 出庫済みの数の合計 */
select @出庫済み数=isnull(sum(出庫済み数),0) from 受注 where
部品コード=@部品コード and 納期<=@日付
/* 出庫予定数 */
set @出庫予定数=@受注済み数-@出庫済み数
/* 実在庫数 */
select @在庫数=isnull(在庫数,0) from 部品 where 部品コード=@部品コード
/* 在庫予定数 を出力パラメータに割り当てる */
set @予定在庫数 = @在庫数+@入庫予定数-@出庫予定数
<リスト7> 「入庫カード」フォームの[削除]ボタン
Private Sub 削除_Click()
DoCmd.RunSQL "exec sp_delete入庫 @入庫ID=" & Me!入庫ID
End Sub
<リスト8> 「受注カード」フォームの[在庫確認]ボタン
Private Sub 在庫確認_Click()
/* ADOによるストアドプロシージャの実行 */
/* ADOオブジェクト(インスタンス)の作成 */
Dim objConn As New ADODB.Connection /* コネクション */
Dim objCmd As New ADODB.Command /* コマンド */
Dim dateStr As String
Dim bcode As String
/* 接続の実際 プロバイダ,ユーザーなどの指定 */
objConn.Open "Provider=SQLOLEDB;Data Source=BF95CL1;Initial
Catalog=PartsStockDBSQL;User ID=sa;Password="
/* 日付をYYYYMMDD に合成 */
dateStr = Year(Me!納期) & _
Right("00" & Month(Me!納期), 2) & _
Right("00" & Day(Me!納期), 2)
bcode = Me!部品コード
/* コマンドの届け先を指定 */
objCmd.ActiveConnection = objConn
/* 実行するコマンドとしてストアドプロシージャを指定 */
objCmd.CommandText = "sp_在庫予定"
/* コマンドがストアドプロシージャであることを通知 */
objCmd.CommandType = adCmdStoredProc
/* パラメータを初期化 */
objCmd.Parameters.Refresh
/* 第1のパラメータを設定 */
objCmd.Parameters(1).Value = dateStr
/* 第2のパラメータを設定 */
objCmd.Parameters(2).Value = bcode
/* コマンドを実行 */
objCmd.Execute
/* 第3のパラメータとして返される値,在庫数を表示 */
MsgBox Nz(objCmd.Parameters(3).Value, 0)
/* リソースの解放 */
objConn.Close
Set objConn = Nothing
Set objCmd = Nothing
End Sub