触发器案例

/*
*触发器学习
*/
--我的第一个触发器
create trigger trig1
on stock
for insert
as 
print '插入成功'
select * from stock
insert into stock values(5,'方便面',100000,'n1','救灾3#',1)

/*
insert触发器 for insert
原理：在用户插入数据时，该行数据同时复制到基本表与内存中的inserted表中，
作用有三：检查插入的数据是否符合规则；在插入的数据中增加数据；级联改动其他的表
*/
--创建trig,在插入数据时验证库存量，小于1时撤销该操作
create trigger trig2
on stock
for insert
as
declare @num int
select @num=stock_num from inserted  
if @num<1
begin
rollback tran
raiserror('amont must be greater than 1',16,10)
end

insert into stock values(5,'方便面',0,'n1','救灾3#',1)
select * from stock
--delete触发器

create trigger trig3
on stock
for delete
as
begin tran
declare @id int
select @id=stock_id from deleted
delete from out_stock where stock_id=@id
commit tran

drop trigger trig3
delete  stock where stock_id=3
--instead of触发器 用于视图
--复合触发器
--不允许修改删除saving表的prj_id=4的列
create trigger trig4
on saving
for update,delete
as
declare @id int
select @id=prj_id from deleted
if @id=4
begin
rollback tran
print 'you can not update or delete this column'
end

delete from saving where prj_id=4