触发器的例子

--创建交易表TRANSLATECREATE TABLE TRANSLATE(T_ID INT PRIMARY KEY,CARD_ID INT ,T_DATE DATETIME,T_TYPE NVARCHAR(8),T_SALARY MONEY)--会触发下面创建的TRIG_UPDATE_CARDONE触发器INSERT INTO TRANSLATE VALUES(5,1,GETDATE(),'存',4000)INSERT INTO TRANSLATE VALUES(6,1,GETDATE(),'取',1000)INSERT INTO TRANSLATE VALUES(4,2,GETDATE(),'取',400)INSERT INTO TRANSLATE VALUES(3,GETDATE(),'取',400)GO--创建TRIG_UPDATE_CARDONE触发器，当交易表TRANSLATE发生交易时（如取钱400），则CARDONE表--就会增加400元--INSERTED逻辑表IF OBJECT_ID('TRIG_UPDATE_CARDONE') IS NOT NULLDROP TRIGGER TRIG_UPDATE_CARDONEGOCREATE TRIGGER TRIG_UPDATE_CARDONEON TRANSLATE FOR INSERTASDECLARE @ID INTDECLARE @CASH MONEYDECLARE @TYPE NVARCHAR(8)SELECT @ID=CARD_ID,@CASH=T_SALARY,@TYPE=T_TYPE FROM INSERTEDIF @TYPE='存'BEGINUPDATE CARDONE SET PAY=PAY+@CASH WHERE ID=@IDENDELSEBEGINUPDATE CARDONE SET PAY=PAY-@CASH WHERE ID=@IDENDGO--创建TRIG_DELETE_CARDONE触发器，当消掉某张卡时，其对应的交易记录就会被删除--DELETEDIF OBJECT_ID('TRIG_DELETE_CARDONE') IS NOT NULLDROP TRIGGER TRIG_DELETE_CARDONEGOCREATE TRIGGER TRIG_DELETE_CARDONEON CARDONE FOR DELETEASDECLARE @ID INTSELECT @ID=ID FROM DELETEDDELETE FROM TRANSLATE WHERE CARD_ID=@IDGODELETE FROM CARDONE WHERE ID=3SELECT * FROM TRANSLATE--UPDATE触发器，有DELETED和INSERTED两张逻辑表--DELETED存储更新前的数据，INSERTED存储更新后的数据--自定义错误RAISERROR('自定义错误',16,1)--DECLARE @A INTRAISERROR('自定义错误%d',16,1,2)

INSERT INTO CARDONE VALUES(1,'TOM',100)INSERT INTO CARDONE VALUES(2,'JIM',10)INSERT INTO CARDONE VALUES(3,'霸天虎',10000)select * from cardone--ACID特性，原子性、一致性、隔离性和持久性SET XACT_ABORT ONBEGIN TRANSACTION UPDATE CARDONE SET PAY=PAY-100 WHERE ID=1UPDATE CARDONE SET PAY=PAY+100 WHERE ID=2COMMIT TRANSACTION--隔离级别是指同一时刻允许多少个事务对同一数据进行操作--事务的并发性(几个事务同时发生)越高，安全性越低。--触发器：表与表之间的约束，自动执行--三种触发器：DELTE INSERT UPDATE--当更新表CARDONE的时候触发器TRIG_UPDATE自动执行，执行的操作是检索出所有的记录IF OBJECT_ID('TRIG_UPDATE','T') IS NOT NULLDROP TRIG_UPDATEGOCREATE TRIGGER TRIG_UPDATEON CARDONE FOR UPDATEASSELECT * FROM CARDONEGOUPDATE CARDONE SET PAY=PAY+10 WHERE ID=2