/*
*触发器学习
*/
--我的第一个触发器
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
触发器案例
最新推荐文章于 2024-01-07 08:30:00 发布