触发器

-----------------触发器----------------

触发器分为事前触发器和事后触发器
/*
--创建触发器
语法：
CREATE TRIGGER 触发器的名称 ON 目标的名称
FOR 针对表的哪一个操作,如:INSERT/UPDATE.. AS
BEGIN
   --触发的事件
   PRINT ‘水电费发生’
END

*/

-------使用NETBERDB----------
USE NetBarDB
go
IF EXISTS (SELECT * FROM SYS.SYSOBJECTS WHERE NAME='TR_INSERT_RECORDINFO')
DROP TRIGGER TR_INSERT_RECORDINFO
GO
--新建TRIGGER
CREATE TRIGGER TR_INSERT_RECORDINFO
ON RECORDINFO FOR INSERT AS
BEGIN
   --定义变量用于存储会员号、卡号、电脑编号
   DECLARE @CARDID INT
   DECLARE @PCID INT
   DECLARE @CARDNUMBER CHAR(10)
   --从INSERTED表中获取插入的记录，包括电脑编号卡号
   SELECT @PCID=PCID,@CARDID=CARDID FROM INSERTED
   --根据电脑编号修改使用的使用状态
   UPDATE PCINFO SET PCUSE =1 WHERE PCID=@PCID
   --根据编号查询会员号
   select @PCId from cardInfo where cardId=@cardId
   --显示上机成功
   print '上机成功'
END
go
set nocount on--不显示T-sql语句影响的行数
declare @cardId int --声明卡的编号
select @cardId = CardId from cardInfo where CardNumber='023-001'
insert into recordInfo (cardId ,PCId ,beginTime ) values (@cardId ,1,GETDATE())

select * from recordInfo
select * from PCInfo

------------------delate 的触发器----------------------------------------------
use NetBarDB
go
if exists (select*from sys.sysobjects where name ='tr_delect_recordInfo')
   drop trigger tr_delect_recordInfo
go
create trigger tr_delect_recordInfo
   on recordInfo for delete as
   print '开始备份RecordInfo数据'
   if exists (select *from sys.sysobjects where name='backRecordInfo')
       insert into backRecordInfo select * from deleted
   else
       select * into backRecordInfo from deleted
   print 'backRecordInfo 表备份数据成功，备份数据为'
   select * from backRecordInfo
go
-----------------
--c测试delete 触发器
delete from recordInfo

select *from recordInfo

-----------新建update-触发器---------------------
use NetBarDB
go
if exists(select * from sys.sysaltfiles where name ='tr_update_recordInfo')
   drop trigger tr_update_recordInfo
go
create trigger tr_update_recordInfo
on recordInfo for update as
    declare @reforePCId int
   declare @atforePCId int
   select from deleted