SQL SERVER 触发器

1、USE masterGOIF EXISTS (SELECT * FROM sysdatabases WHERE NAME=SuperMarketDB)DROP DATABASE SuperMarketDBGOCREATE DATABASE SuperMarketDBGOUSE SuperMarketDBGO/*-建表-*/IF EXISTS (SELECT * FROM sysobjects WHERE name=OrderInfo) DROP TABLE OrderInfoIF EXISTS (SELECT * FROM sysobjects WHERE name=StockInfo) DR

2、OP TABLE StockInfoIF EXISTS (SELECT * FROM sysobjects WHERE name=GoodsInfo) DROP TABLE GoodsInfoGO/*-创建商品信息表-*/CREATE TABLE GoodsInfo(GoodsID int not null, -定义商品的编号 GoodsName varchar(20) not null, -定义商品的名称 GoodsPrice money not null -定义商品的价格)/*-添加商品信息表的约束-*/ALTER TABLE GoodsInfo ADD CONSTRAINT PK_Goo

3、dsID PRIMARY KEY(GoodsID) -主键约束GO-定单信息表CREATE TABLE OrderInfo( GoodsID int not null, -商品编号 GoodsName varchar(20) not null, -商品名称 SaleAmount int not null, -定单数量 OrderDate datetime not null -定单日期)GO/*-添加约束-*/ALTER TABLE OrderInfo -定单数量必须大于 ADD CONSTRAINT SaleAmount CHECK(SaleAmount0)ALter TABLE OrderI

4、nfo -定单日期默认为当前日期 ADD CONSTRAINT DF_OrderDate DEFAULT(GETDATE() FOR OrderDateGO-商品库存信息表CREATE TABLE StockInfo ( GoodsID int not null, -商品编号 StockAmount int not null -商品库存数量)GO/*-添加约束-*/ALTER TABLE StockInfo ADD CONSTRAINT FK_GoodsID FOREIGN KEY (GoodsID)REFERENCES GoodsInfo (GoodsID) -给库存信息表添加外键约束GO/

5、*-插入测试数据：往商品信息表和库存表里面插入信息-*/INSERT INTO GoodsInfo VALUES (1,联想牌MP3,523)GOINSERT INTO StockInfo(GoodsID,StockAmount) VALUES (1,50)GO/*-往定单表插入定单信息-*/INSERT INTO OrderInfo(GoodsID,GoodsName,SaleAmount) VALUES (1,联想牌MP3,1)/*一、插入商品信息表 INSERT触发器*/IF EXISTS(SELECT * FROM sysobjects WHERE NAME=Tri_GoodsInfo

6、_Insert)DROP TRIGGER Tri_GoodsInfo_InsertGOCREATE TRIGGER Tri_GoodsInfo_InsertON GoodsInfo FOR INSERTASDECLARE GoodsID INT SELECT GoodsID=GoodsID FROM insertedINSERT INTO StockInfo VALUES (GoodsID,0)GOINSERT INTO GoodsInfo VALUES (2,戴尔MP3,524)INSERT INTO GoodsInfo VALUES (3,英特尔MP3,525)/*二、删除订单表中记录 D

7、ELETE 触发器*/IF EXISTS(SELECT * FROM sysobjects WHERE NAME=Tri_OrderInfo_Delete)DROP TRIGGER Tri_OrderInfo_DeleteGOCREATE TRIGGER Tri_OrderInfo_DeleteON OrderInfo FOR DELETEASDECLARE MaxDate DATETIMESELECT MaxDate=MAX(OrderDate) FROM deletedIF DATEDIFF(MONTH,MaxDate,GETDATE()1BEGINPRINT 备份中.IF EXISTS(

8、SELECT * FROM sysobjects WHERE NAME=BackupOrderInfo)INSERT INTO BackupOrderInfo SELECT * FROM deletedELSE SELECT * INTO BackupOrderInfo FROM deletedPRINT 备份数据成功！END-测试触发器SET NOCOUNT ON-修改商品库存表UPDATE StockInfo SET StockAmount=50 WHERE GoodsID=2UPDATE StockInfo SET StockAmount=50 WHERE GoodsID=3-插入订单信息INSERT INTO OrderInfo(GoodsID,GoodsName,SaleAmount) VALUES (2,戴尔MP3,10)INSERT INTO OrderInfo(GoodsID,GoodsN