Lesson触发器

log_date datetime
)
alter trigger trigger_orderinfo_all
on
orderinfo
for
insert , delete , update
as
declare @goodsid int, @goodsname varchar(50), @saleAmount int , @orderdate datetime
USE MASTER
GO
IF EXISTS (SELECT * FROM SYSDATABASES WHERE NAME='SuperMarketDB')
DROP DATABASE SuperMarketDB
GO
CREATE DATABASE SuperMarketDB
GO
USE SuperMarketDB
from
inserted
set @tempStr = 'id:' + convert(varchar(10),@goodsid) + ' goodsname:' + @goodsname
+ ' saleamount:' + convert(varchar(10),@saleamount) + ' orderdate:' +convert(varchar(50),@orderdate)
(
GoodsID int not null, --商品编号
StockAmount int not null --商品库存数量
)
GO
GO
/*---插入测试数据：往商品信息表和库存表里面插入信息---*/
INSERT INTO GoodsInfo VALUES (1,'联想牌MP3',523)
insert into orderinfo values(1,@goodsName,@saleamount,getDate())
create trigger trigger_orderinfo_insert
on
orderinfo
for
insert
as
print '商品卖出了若干个!'
select @goodsid = goodsid , @goodsname = goodsname ,
@saleamount = saleamount , @orderdate = orderdate
from inserted
print 'id :' + convert(varchar(10),@goodsid) + ' goodsname : ' + @goodsname
SaleAmount int not null, --定单数量
OrderDate datetime not null --定单日期
)
GO
/*------------------------添加约束----------------*/
CREATE TABLE StockInfo --商品库存信息表
+ ' saleamount:' + convert(varchar(10),@saleamount) + ' orderdate:' + convert(varchar(50),@orderdate)
insert into orderinfo_log values('修改',@tempStr , @tempStr2 , getDate() )
(
GoodsID int not null, --定义商品的编号
GoodsName varchar(20) not null, --定义商品的名称
GoodsPrice money not null --定义商品的价格
)
/*--------添加商品信息表的约束------*/
orderinfo
instead of
delete
as
print '删除'
create trigger trigger_orderinfo_delete
on
orderinfo --从表
DROP TABLE StockInfo
IF EXISTS (SELECT * FROM sysobjects WHERE name='GoodsInfo')
DROP TABLE GoodsInfo
GO
/*--------创建商品信息表-------*/
CREATE TABLE GoodsInfo
@orderdate = orderdate
from
inserted
set @tempStr = 'id:' + convert(varchar(10),@goodsid) + ' goodsname:' + @goodsname
+ ' saleamount:' + convert(varchar(10),@saleamount) + ' orderdate:' + convert(varchar(50),@orderdate)
as
insert into orderinfo values(@goodsid,@goodsName,@saleamount,getDate())
update stockinfo set stockamount = stockamount - @saleamount
where
goodsid = @goodsid
alter trigger trigger_orderinfo_insert
on
orderinfo
for
update
as
select * from deleted
select * from inserted
select * from orderinfo
update orderinfo set goodsname = 'mp4' where goodsid = 12
ALTER TABLE GoodsInfo ADD CONSTRAINT PK_GoodsID PRIMARY KEY(GoodsID) --主键约束
GO
EATE TABLE OrderInfo --定单信息表
(
GoodsID int not null, --商品编号
GoodsName varchar(20) not null, --商品名称
insert into orderinfo_log values('删除', '', @tempStr , getDate() )
end
select * from orderinfo
select * from orderinfo_log
insert into orderinfo values(1,'mp3',3,getdate())
end
else if exists(select * from inserted)
begin
select @goodsid = goodsid , @goodsname = goodsname , @saleamount = saleamount ,
@orderdate = orderdate
select @goodsid = goodsid , @goodsname = goodsname , @saleamount = saleamount ,
@orderdate = orderdate
from
deleted
set @tempStr2 = 'id:' + convert(varchar(10),@goodsid) + ' goodsname:' + @goodsname
@orderdate = orderdate
from
deleted
set @tempStr = 'id:' + convert(varchar(10),@goodsid) + ' goodsname:' + @goodsname
+ ' saleamount:' + convert(varchar(10),@saleamount) + ' orderdate:' + convert(varchar(50),@orderdate)
for
update
as
if(update(goodsname))
begin
print 'goodsname 被更改'
end
--替代触发器
select * from orderinfo
create trigger trigger_orderinfo_delete
on
SELECT * FROM OrderInfo
SELECT * FROM GoodsInfo
SELECT * FROM StockInfo
create proc pro_market