1，创建商品表（Store），订单表（orders），日志表（Logs）
第一步没什么讲解，我们创建表，并加一些示例数据。
示例代码：
1 create table Store 2 ( 3 ID uniqueidentifier primary key, 4 ProductID int not null, 5 ProductPrice money not null default 1, 6 ProductCH nvarchar(80) not null, 7 ProductDate datetime not null, 8 NowNumber int not null 9 ); 10 create table orders 11 ( 12 OrderID int primary key, 13 ProductID int not null, 14 BuyNumber int Not null default 1, 15 BuyPricr money not null, 16 NowOrderPrice money default 0 17 ) 18 create table Logs 19 ( 20 ID uniqueidentifier primary key, 21 operatedatetime datetime, 22 ProductID int, 23 oldprice money, 24 newprice money 25 ); 26 27 insert into dbo.Store 28 values(NEWID(),1001,5000,'联想','2011-9-1',50) 29 insert into dbo.Store 30 values(NEWID(),1002,6000,'apple','2011-9-1',50) 31 insert into dbo.orders(OrderID,ProductID,BuyNumber,BuyPricr) 32 values(10013,1001,5,6000)
2，创建订单表触发器
要求2,3类似操作我在一起讲解了。
先看示例代码：
1 create trigger tri_order_NowOrderPrice 2 on orders after insert 3 as 4 begin 5 declare @NowOrderPrice money; 6 declare @BuyNumber int; 7 declare @ProductID int; 8 select @ProductID= ProductID,@BuyNumber= BuyNumber from inserted; 9 select @NowOrderPrice=inserted.BuyNumber * inserted.BuyPricr from inserted; 10 update orders set NowOrderPrice=@NowOrderPrice where ProductID=@ProductID; 11 update Store set NowNumber=NowNumber-@BuyNumber where ProductID=@ProductID; 12 end; 13 14 create trigger tri_store_NowOrderPrice2 15 on orders after update 16 as 17 begin 18 declare @NowOrderPrice money; 19 declare @ProductID int; 20 declare @BuyNumber1 int; 21 declare @BuyNumber2 int; 22 select @ProductID= ProductID from inserted; 23 select @BuyNumber1=BuyNumber from inserted; 24 select @NowOrderPrice=inserted.BuyNumber * inserted.BuyPricr from inserted; 25 select @BuyNumber2=deleted.BuyNumber from deleted where ProductID=@ProductID; 26 update orders set NowOrderPrice=@NowOrderPrice where ProductID=@ProductID; 27 update Store set NowNumber=NowNumber-(@BuyNumber1-@BuyNumber2) where ProductID=@ProductID; 28 end;
触发器的关键字是trigger，语法是，on 表名 after 操作名称（一般为insert，update，delete），begin end中写一些触发器的处理操作。
inserted获取insert之后的数据。语法就是这么简单。
3，创建日志表触发器
日志表触发器要做的操作就是记录商品价格变化，那对应就应该在商品表中创建触发器。
示例代码：
1 create trigger tri_NowOrderPrice4 2 on dbo.Store after insert,update,delete 3 as 4 begin 5 if exists(select *from inserted) and exists(select *from deleted) 6 begin 7 print('update'); 8 declare @price1 money; 9 declare @price2 money; 10 declare @datetime datetime; 11 select @price1=ProductPrice from deleted; 12 select @price2=ProductPrice from inserted; 13 if @price1!=@price2 14 begin 15 declare @ProductID int; 16 select @ProductID=ProductID from inserted 17 insert into Logs(ID,operatedatetime,ProductID,oldprice,newprice) 18 values(newid(),convert(datetime,getdate()),@ProductID,@price1,@price2) 19 20 select *from dbo.Logs 21 end 22 end 23 else if exists(select *from inserted) 24 begin 25 print('insert'); 26 end 27 else if exists(select *from deleted) 28 begin 29 print('delete'); 30 end 31 else 32 begin 33 print('others'); 34 end 35 end
触发器就是这些内容，有关触发器的一些复杂操作希望大家有时间研究下，讲的不好请大家多多指正，希望大家学好t-sql语言。
以后继续整理编程相关内容，希望大家多多关注。。。。
本文转自田园里的蟋蟀博客园博客，原文链接：http://www.cnblogs.com/xishuai/p/3374351.html，如需转载请自行联系原作者
