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

触发器 分为 事前触发器 和事后触发器

/*

--创建触发器

语法：

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