機房收費系統之觸發器

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表！';