资源描述：
《SQL SERVER 触发器》由会员上传分享，免费在线阅读，更多相关内容在工程资料-天天文库。
1、USEmasterGOIFEXISTS(SELECT*FROMsysdatabasesWHERENAME='SuperMarketDB')DROPDATABASESuperMarketDBGOCREATEDATABASESuperMarketDBGOUSESuperMarketDBGO/*---------------------------建表-----------------*/IFEXISTS(SELECT*FROMsysobjectsWHEREname='OrderInfo')DROPTABLEOrderInfoIF
2、EXISTS(SELECT*FROMsysobjectsWHEREname='StockInfo')DROPTABLEStockInfoIFEXISTS(SELECT*FROMsysobjectsWHEREname='GoodsInfo')DROPTABLEGoodsInfoGO/*--------创建商品信息表-------*/CREATETABLEGoodsInfo(GoodsIDintnotnull,--定义商品的编号GoodsNamevarchar(20)notnull,--定义商品的名称GoodsPricemone
3、ynotnull--定义商品的价格)/*--------添加商品信息表的约束------*/ALTERTABLEGoodsInfoADDCONSTRAINTPK_GoodsIDPRIMARYKEY(GoodsID)--主键约束GO--------------------------------------------定单信息表CREATETABLEOrderInfo(GoodsIDintnotnull,--商品编号GoodsNamevarchar(20)notnull,--商品名称SaleAmountintnotnull,-
4、-定单数量OrderDatedatetimenotnull--定单日期)GO/*------------------------添加约束----------------*/ALTERTABLEOrderInfo--定单数量必须大于０ ADDCONSTRAINTSaleAmountCHECK(SaleAmount>0)ALterTABLEOrderInfo--定单日期默认为当前日期ADDCONSTRAINTDF_OrderDateDEFAULT(GETDATE())FOROrderDateGO-----------------
5、---------------------商品库存信息表CREATETABLEStockInfo(GoodsIDintnotnull,--商品编号StockAmountintnotnull--商品库存数量)GO/*----------添加约束---------*/ALTERTABLEStockInfoADDCONSTRAINTFK_GoodsIDFOREIGNKEY(GoodsID)REFERENCESGoodsInfo(GoodsID)--给库存信息表添加外键约束GO/*---插入测试数据：往商品信息表和库存表里面插入信息
6、---*/INSERTINTOGoodsInfoVALUES(1,'联想牌MP3',523)GOINSERTINTOStockInfo(GoodsID,StockAmount)VALUES(1,50)GO/*----往定单表插入定单信息---------*/INSERTINTOOrderInfo(GoodsID,GoodsName,SaleAmount)VALUES(1,'联想牌MP3',1)/*******************一、插入商品信息表INSERT触发器*********************/IFEXIST
7、S(SELECT*FROMsysobjectsWHERENAME='Tri_GoodsInfo_Insert')DROPTRIGGERTri_GoodsInfo_InsertGOCREATETRIGGERTri_GoodsInfo_InsertONGoodsInfoFORINSERTASDECLARE@GoodsIDINTSELECT@GoodsID=GoodsIDFROMinsertedINSERTINTOStockInfoVALUES(@GoodsID,0)GOINSERTINTOGoodsInfoVALUES(2,'戴
8、尔MP3',524)INSERTINTOGoodsInfoVALUES(3,'英特尔MP3',525)/***************二、删除订单表中记录DELETE触发器***********************/IFEXISTS(SELECT*FROMsysobjectsWHERE
