--创建交易表TRANSLATE CREATE 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 NULL DROP TRIGGER TRIG_UPDATE_CARDONE GO CREATE TRIGGER TRIG_UPDATE_CARDONE ON TRANSLATE FOR INSERT AS DECLARE @ID INT DECLARE @CASH MONEY DECLARE @TYPE NVARCHAR(8) SELECT @ID=CARD_ID,@CASH=T_SALARY,@TYPE=T_TYPE FROM INSERTED IF @TYPE='存' BEGIN UPDATE CARDONE SET PAY=PAY+@CASH WHERE ID=@ID END ELSE BEGIN UPDATE CARDONE SET PAY=PAY-@CASH WHERE ID=@ID END GO --创建TRIG_DELETE_CARDONE触发器,当消掉某张卡时,其对应的交易记录就会被删除 --DELETED IF OBJECT_ID('TRIG_DELETE_CARDONE') IS NOT NULL DROP TRIGGER TRIG_DELETE_CARDONE GO CREATE TRIGGER TRIG_DELETE_CARDONE ON CARDONE FOR DELETE AS DECLARE @ID INT SELECT @ID=ID FROM DELETED DELETE FROM TRANSLATE WHERE CARD_ID=@ID GO DELETE FROM CARDONE WHERE ID=3 SELECT * FROM TRANSLATE --UPDATE触发器,有DELETED和INSERTED两张逻辑表 --DELETED存储更新前的数据,INSERTED存储更新后的数据 --自定义错误 RAISERROR('自定义错误',16,1) -- DECLARE @A INT RAISERROR('自定义错误%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 ON BEGIN TRANSACTION UPDATE CARDONE SET PAY=PAY-100 WHERE ID=1 UPDATE CARDONE SET PAY=PAY+100 WHERE ID=2 COMMIT TRANSACTION --隔离级别是指同一时刻允许多少个事务对同一数据进行操作 --事务的并发性(几个事务同时发生)越高,安全性越低。 --触发器:表与表之间的约束,自动执行 --三种触发器:DELTE INSERT UPDATE --当更新表CARDONE的时候触发器TRIG_UPDATE自动执行,执行的操作是检索出所有的记录 IF OBJECT_ID('TRIG_UPDATE','T') IS NOT NULL DROP TRIG_UPDATE GO CREATE TRIGGER TRIG_UPDATE ON CARDONE FOR UPDATE AS SELECT * FROM CARDONE GO UPDATE CARDONE SET PAY=PAY+10 WHERE ID=2