触发器 测试~！学习~！ 实例~！

if  object_id ('Classmate') is not null drop table Classmate                --查看是否存在表"Classmate" 如果存在 删除~！

go

create table Classmate(              --创建"classmate"表
ID int identity(1,1) primary key ,
CNAME varchar(50),
Quantity numeric(38,2),
Price numeric(38,2)
)
go
alter table Classmate add Amount numeric         --在"Classmate"表里面添加一个字段
go

if exists (select name from sysobjects where xtype='TR' and name='Calc_Trigger')  --查看书否存在触发器”Calc_Check“
drop trigger Calc_Trigger                --如果存在，则删除触发器”Calc_Check“

go

create trigger Calc_Trigger                --创建触发器Calc_Trigger
on Classmate
for insert
as
begin transaction
 update Classmate set Amount=Quantity*Price where CNAME in (
 select CNAME from inserted)
commit transaction

go

insert into  Classmate(CNAME,Quantity,PRICE ) select 'bean','5','100' union all select  --插入测试数据
'andy','6','100' union all select
'hackman','7','200' union all select
'jake','8','0'

go

if exists (select name from sysobjects where xtype='TR' and name='Check_Trigger')        --判断触发器'Check_Trigger'是否存在
drop Trigger Check_Trigger                 --如果存在，则删除~！

go

create Trigger Check_Trigger                --创建触发器‘Check_Trigger’
on classmate
for insert
as
begin
 if not exists(select Quantity from Classmate where CNAME in (
 select CNAME from Inserted)) 
 begin
  raiserror('所插入的数据不存在',16,1)
  rollback
 end
 if exists (select Quantity from classmate where CNAME in (
 select CNAME from inserted ) and Quantity <=0)
 begin
  raiserror('插入数量小于等于0',16,1)
  rollback
 end
end

go

insert into  Classmate(CNAME,Quantity,PRICE ) select 'bean','5','100' union all select
'andy','6','100' union all select
'hackman','7','200' union all select
'jake','0','0'

go

insert into  Classmate(Quantity,PRICE ) select '5','100' union all select
'6','100' union all select
'7','200' union all select
'30','40'

go

select * from classmate