触发器

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

触发器 分为 事前触发器 和事后触发器
/*
--创建触发器
语法：
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