复习sqlserver中触发器的知识点

/*
触发器知识复习
触发器语法
CREATE TRIGGER trigger_name
ON table_name
[WITH encryption] --是否加密
FOR [DELETE,INSERT,UPDATE]
AS
T_SQL语句
GO
触发器中涉及到2张虚拟表(deleted,inserted)
*/
--
 
ALTER TRIGGER traninfotrigger1
ON traninfo
FOR INSERT
AS
DECLARE @type VARCHAR(20)
DECLARE @money MONEY
DECLARE @cid int
SELECT @cid = cid, @type = transType, @money = transMoney FROM inserted
IF(@type = '存')
BEGIN
UPDATE bank SET currentMoney = currentMoney + @money WHERE cid = @cid
END
ELSE
BEGIN
UPDATE bank SET currentMoney = currentMoney - @money WHERE cid = @cid
END
GO
--
CREATE TRIGGER traninfotrigger2
ON traninfo
FOR INSERT
AS
DECLARE @type VARCHAR(20)
DECLARE @money MONEY
DECLARE @cid int
SELECT @cid = cid, @type = transType, @money = transMoney FROM inserted
IF(@type = '取')
BEGIN
SET @money = -@money
END
UPDATE bank SET currentMoney = currentMoney + @money WHERE cid = @cid
GO
--
INSERT INTO traninfo (cid, cdate, transType, transMoney) VALUES (1, GETDATE(), '取', 200)
SELECT * FROM traninfo
SELECT * FROM bank
 
INSERT INTO traninfo (cid, cdate, transType, transMoney) VALUES (3, GETDATE(), '存', 800)
GO
 
/*
注意点：
当SET IDENTITY_INSERT 表名 ON的时候,如果向数据表中插入数据，不能直接写
INSERT INTO 表名2 SELECT * FROM 表名1这样的语句来进行插入数据，要将表名2和表1的列显示的写出才行。
*/
--
 
CREATE TRIGGER traninfotrigger3
ON traninfo
FOR DELETE
as
	IF(NOT EXISTS(SELECT * FROM sysobjects WHERE name = 'traninfoBackup'))
		BEGIN
			SELECT * INTO traninfoBackup FROM deleted 
		END
	ELSE
		BEGIN
			--SET IDENTITY_INSERT traninfoBackup ON
			INSERT INTO 
				traninfoBackup(cid, cdate, transType, transMoney) 
			SELECT 
				cid, cdate, transType, transMoney 
			FROM 
				deleted
		--INSERT INTO traninfoBackup SELECT * FROM traninfo
		END	
GO
 
SELECT * FROM traninfo
--SET IDENTITY_INSERT traninfoBackup ON
DELETE FROM traninfo WHERE tid <= 3
 
DELETE FROM traninfo WHERE tid = 27
SELECT * FROM traninfoBackup
--SET IDENTITY_INSERT traninfoBackup OFF
INSERT INTO traninfoBackup (cid, cdate, transType, transMoney) VALUES (1, GETDATE(), '取', 200)
GO
--
 
CREATE TRIGGER bank5
ON bank
FOR UPDATE
as
	DECLARE @oldMoney MONEY
	DECLARE @newMoney MONEY
	DECLARE @tranMoney MONEY
 
	SELECT @oldMoney = currentMoney FROM deleted
	SELECT @newMoney = currentMoney FROM inserted
	SET @tranMoney = @oldMoney - @newMoney
	PRINT '******************************************************************'
	PRINT 'oldMoney = ' + CONVERT(VARCHAR(20), @oldMoney) + '		' + 
	'newMoney = ' + CONVERT(VARCHAR(20), @newMoney) + '		' + 
	'交易金额 = ' + CONVERT(VARCHAR(20), @tranMoney)
	PRINT '******************************************************************'
	IF(@tranMoney >= 20000 OR @tranMoney <= -20000)
		BEGIN
			PRINT '发生错误，交易金额最多两万'
			SELECT * FROM bank
			ROLLBACK TRANSACTION --回滚
		END
GO
 
 
INSERT INTO traninfo (cid, cdate, transType, transMoney) VALUES (1, GETDATE(), '存', 13000)
 
 
 
SELECT * FROM bank
GO
 
/*
列级触发器
UPDATE(列名) 判断该列是否修改
*/
CREATE TRIGGER tri_update_traninfo
 ON traninfo
  FOR UPDATE
 AS
	IF UPDATE(cdate)
	 BEGIN
		PRINT '交易失败'
		RAISERROR('安全警告:交易日期不能修改，由系统自动产生', 16, 1)
		SELECT * FROM traninfo
		ROLLBACK TRANSACTION
	 END
GO
 
 
UPDATE traninfo SET cdate = GETDATE()
SELECT * FROM traninfo
 
 
-- TRUNCATE TABLE traninfo
/*
TRUNCATE TABLE 表名 虽然也能删除表中的数据，但是TRUNCATE TABLE是DDL(数据定义语言，所以不能带where条件)，如果要根据条件来删
除表中的某些数据的话必须要使用delete(DML 数据操作语言)
*/