机房收费系统之触发器

CREATE TRIGGER [dbo].[TR_charge_U]	--创建触发器
	
ON [dbo].[T_Recharge]	--在T_ReCharge表

for insert	为了插入数据而需要执行的操作

AS 
--定义一些参数
declare @cardID char(10)	--卡号
declare @cash decimal(10, 2)	--卡内余额
declare @addmoney decimal(10, 2)	--充值金额
if UPDATE(cash)

BEGIN
	select @cardID =CardID from inserted 	--选择充值表中的卡号	
	select @addmoney=cash from inserted --选择我们充值表中的充值金额
	select @cash =T_card.cash from T_Card 	--选择卡内的余额
	update T_Card set Cash=@cash+@addmoney where cardID =@cardID 
END

create trigger tgr_classes_insert
	on classes
 	for insert --插入触发
	as
 --定义变量
 declare @id int, @name varchar(20), @temp int;
 --在inserted表中查询已经插入记录信息
 select @id = id, @name = name from inserted;
 set @name = @name + convert(varchar, @id);
 set @temp = @id / 2; 
 insert into student values(@name, 18 + @id, @temp, @id);

create trigger tgr_classes_delete
on classes
 for delete --删除触发
as
 print '备份数据中……'; 
 if (object_id('classesBackup', 'U') is not null)
 --存在classesBackup，直接插入数据
 insert into classesBackup select name, createDate from deleted;
 else
 --不存在classesBackup创建再插入
 select * into classesBackup from deleted;
 print '备份数据成功！';

create trigger tgr_classes_update
 on classes
 for update
 as
 declare @oldName varchar(20), @newName varchar(20);
 --更新前的数据
 select @oldName = name from deleted;
 if (exists (select * from student where name like '%'+ @oldName + '%'))
 begin
 --更新后的数据
 select @newName = name from inserted;
 update student set name = replace(name, @oldName, @newName) where name like '%'+ @oldName + '%';
 print '级联修改数据成功！';
 end
 else
 print '无需修改student表！';