【正文】 t null,UserArea varchar(50) not null,)銷售商表（Distributer）字段名字段類型長度主鍵字段值約束對應(yīng)中文名DistIDvarchar10PNot null銷售商編號DistNameVarchar25Not null銷售商名稱DistareaVarchar50Not null銷售商地址DistStyleIntNot null銷售商類型DistPhoneVarchar20Not null 聯(lián)系方式DistPWVarchar16Not Null銷售商密碼create table Distributer(DistID varchar(10) not null primary key,DistName varchar(25) not null,Distarea varchar(50) not null,DistStyle int not null,DistPhone varchar(20) not null,DistPW varchar(16) not null,)分類匯總表（C_Summery）字段名字段類型長度主鍵字段值約束對應(yīng)中文名C_Novarchar10PNot null分類匯總編號ProductStyleVarchar10Not null商品類型ProductNameVarchar50Not null商品名稱Higest_PriceMoneyNot null最高價Lowest_PriceMoneyNot null最低價Bulk_PriceMoneyNot null 大宗價Ave_PriceMoneyNot null平均價ProductIDvarchar10Not null農(nóng)產(chǎn)品編號create table C_Summery(C_No varchar(10) not null primary key,ProductStyle varchar(10) not null,ProductName varchar(50) not null,Higest_Price money not null,Lowest_Price money not null,Bulk_Price money not null,Ave_Price money not null,ProductID varchar(10) not null foreign key references ProductInfo(ProductID),)2. 數(shù)據(jù)庫關(guān)系圖3. 觸發(fā)器的編寫CREATE TRIGGER UserInfo_delete1 ON UserInfo for DeleteAS begin delete Distributer from Distributer dt,deleted d where =END GO/*在用戶表中刪除用戶的同時將銷售商表中對應(yīng)的信息刪除掉*/CREATE TRIGGER UserInfo_delete ON UserInfo for DeleteAS begin delete Supplier from Supplier sp,deleted d where =EndGo/*在用戶表中刪除用戶的同時將供應(yīng)商表中的對應(yīng)的信息刪除掉*/CREATE TRIGGER UserInfo_insert ON UserInfo for insertAS declare UserID varchar(10), UserName Varchar(25), UserPW Varchar(16), UserStyle Int, UserPhone Varchar(20), UserArea Varchar(50) select UserID=UserID, UserName=UserName, UserPW=UserPW, UserStyle=UserStyle, UserPhone=UserPhone, UserArea=UserArea from inserted where UserStyle=39。begin insert into Distributer(DistID,DistName,DistPW,DistPhone,DistStyle,DistArea) values(UserID,UserName,UserPW,UserPhone,UserStyle,UserArea)EndGo/*當(dāng)在用戶表中插入的信息中“用戶類型”為“003”或“004”時，在銷售商表中插入相同的信息*/CREATE TRIGGER UserInfo_update ON UserInfofor UPDATEAS if Update(UserID) BEGIN Update Supplier set SupID= from Supplier sp,deleted d,inserted i where =EndGo/*在用戶表更新用戶信息時將供應(yīng)商表中相應(yīng)的信息進(jìn)行同步更新*/CREATE TRIGGER UserInfo_update1 ON UserInfofor UPDATEAS if Update(UserID) BEGIN Update Distributer set DistID= from Distributer dt,deleted d,inserted i where =END Go/*在用戶表更新用戶信息時將銷售商表中相應(yīng)的信息進(jìn)行同步更新*/CREATE TRIGGER Product_delete ON ProductInfofor DeleteAS begin delete C_Summery from C_Summery cs,deleted d where =END Go/*在用戶表刪除農(nóng)產(chǎn)品信息時將匯總表中相應(yīng)的信息進(jìn)行刪除*/ CREATE TRIGGER Product_update ON ProductInfofor updateAS begin update C_Summery set ProductName= from C_Summery cs,deleted d,inserted i where =END Go/*在用戶表更新農(nóng)產(chǎn)品信息時將匯總表中相應(yīng)的信息進(jìn)行同步更新*/4. 存儲過程概述：以下所有的存儲過程：插入：表示插入一條新記錄（如果記錄已經(jīng)存在，則插入失敗）。239。修改時，每個字段都不能為空，不然修改不了)If exists(select name from sysobjects where name =39。,Suparea=39。)Drop procedure del_Supplier 。If(SupID39。)set where=where+39。+39。+CONVERT(VARCHAR(10),SupStyle)if(SupPhone39。)set where=where+39。+sqlENDbegin transactionexec (sql) If(error0)BeginRollback transactionEndElsebegin Commit transactionEnd調(diào)用查詢存儲過程：use 每日農(nóng)產(chǎn)品價格管理Exec select_Supplier SupID=39。, SupPhone=39。GOCreate procedure add_ProductInfoProductID varchar(10),ProductStyle varchar(10),ProductName varchar(50),ProductOrigin varchar(20),ProductPrice Money,SupID varchar(10),DistID varchar(10)AsBegin transactionInsert into ProductInfo values (ProductID, ProductStyle, ProductName, ProductOrigin, ProductPrice,SupID,DistID)If(error!=0)BeginRollback transactionEndElsebegin Commit transactionEnd調(diào)用插入存儲過程：use 每日農(nóng)產(chǎn)品價格管理Exec add_ProductInfo ProductID=39。,ProductPrice=122．修改If exists(select name from sysobjects where name =39。,ProductName=39。4. 查詢If exists(select name from sysobjects where name =39。39。)set where=where+39。 and 39。+39。ProductPrice =39。+SupID 。set sql=39。39。139。1239。239。del_User39。GOCreate procedure select_UserUserID varchar(10),UserName varchar(25),UserPW varchar(16),UserStyle int,UserPhone varchar(20),UserArea varchar(50)AsBeginDeclare where varchar(150)Declare sql varchar(250)Set where=39。+UserID if(UserName39。)set where=where+39。 and 39。ProductPrice =39。Select * from [User] where 39。39。GOCreate procedure add_DistributerDistID varchar(10),DistName varchar(25),Distarea varchar(50),DistStyle Int,DistPhone varchar(20),DistPW varchar(16)AsBegin transactionInsert into Distributer values (DistID, DistName, Distarea, DistStyle, DistPhone,DistPW)If(error!=0)BeginRollback transactionEndElsebegin Commit transactionEnd調(diào)用插入存儲過程：use 每日農(nóng)產(chǎn)品價格管理Exec add_Distributer DistID =39。2．修改If exists(select name from sysobjects where name =39。,Distarea=39。If(error!=0)BeginRollback transactionEndElsebegin Commit transactionEnd調(diào)用修改存儲過程：use 每日農(nóng)產(chǎn)品價格管理Exec del_Distributer DistID=39。error=0 39。 if(DistName39。39。)set where=where+39。 and 39。+39。,DistName =39。,DistPW=39。139。139。GOCreate procedure del_C_SummeryC_No varchar(10)AsBegin transactionDelete from C_Summery where C_No =C_No 。Set where=39。+C_No。if(ProductName39。39。)set where=where+39。 and 39。+39。 ProductID =39。39。39。每個人都有自己的模塊設(shè)計，如組長蔣新明做觸發(fā)器這個模塊，張傳炎做數(shù)據(jù)存儲這個模塊，李梓勇做數(shù)據(jù)字典這個模塊，孫昌茂建表和最后整合數(shù)據(jù)庫