SQL|使用SQL语句创建并检验触发器

1、为数据库表SC 创建一触发器：当插人或修改一个记录时，确保此记录的成绩在0 ～ 100 分之间。

(1)创建触发器：

USE jiaoxuedb
GO
CREATE TRIGGER T1
	ON SC FOR INSERT,UPDATE
	AS DECLARE @SCORE_READ TINYINT 
	SELECT @SCORE_READ=SCORE FROM inserted
	IF @SCORE_READ>=0 AND @SCORE_READ <=100
	BEGIN
		PRINT'操作完成！'
		RETURN
	END
	ELSE
	BEGIN
	    PRINT'成绩值不在0~100之间！请重新输入'
		ROLLBACK TRAN
	END
GO

(2)检验触发器：

USE jiaoxuedb
GO
INSERT INTO SC VALUES ('01002','001101','190')
GO
INSERT INTO SC VALUES ('01002','001102','100')
GO
UPDATE SC SET SCORE=120 WHERE CNO='01002' AND SNO='001201'
GO
UPDATE SC SET SCORE=60  WHERE CNO='01002' AND SNO='001201'
GO

2、为教师表T创建一触发器：男职工年龄不能超过60周岁，女职工职称是“教授”的年龄不能超过60 岁，其他女职工年龄不能超过55岁。

(1)创建触发器：

USE jiaoxuedb
GO
CREATE TRIGGER T2
	ON Teacher FOR INSERT,UPDATE
	AS DECLARE @TAGE TINYINT    --定义年龄变量
	DECLARE @TSEX varchar(2)	--定义性别变量
	DECLARE @TPROF varchar(10)  --定义职称变量 
	SELECT @TAGE=Age FROM inserted
	SELECT @TPROF=Prof FROM inserted
	SELECT @TSEX=Sex FROM inserted
	if(@TSEX='男' and @TAGE>60) or
     (@TSEX='女' and @TAGE>60 and @TPROF='教授') or
     (@TSEX='女' and @TAGE>55 and @TPROF<>'教授')
     BEGIN
     PRINT '你输入的数据不满足要求,请核对后再进行输入!'
     ROLLBACK TRAN
     END
ELSE BEGIN
     PRINT '你输入的数据没有问题,数据已经插入数据库中!'
     COMMIT TRAN
END
GO

(2)检验触发器：

USE jiaoxuedb
GO
INSERT INTO Teacher VALUES ('000011','容嬷嬷','女','61','教授','1000','1200','计算机')
GO
INSERT INTO Teacher VALUES ('000012','华妃','女','60','教授','1000','1200','计算机')
GO
INSERT INTO Teacher VALUES ('000013','甄嬛','女','56','副教授','1000','1200','计算机')
GO
INSERT INTO Teacher VALUES ('000014','皇后','女','55','副教授','1000','1200','计算机')
GO
INSERT INTO Teacher VALUES ('000015','果郡王','男','60','副教授','1000','1200','计算机')
GO
INSERT INTO Teacher VALUES ('000016','胤禛','男','61','副教授','1000','1200','计算机')
GO

3、为Course，TC，SC三表创建参照完整性：级联删除和级联修改触发器

USE jiaoxuedb
GO
CREATE TRIGGER TRIGGER_DC
	ON Course FOR DELETE
	AS DECLARE @CNO_DEL CHAR(2)
	SELECT @CNO_DEL=Cno FROM deleted
	DELETE FROM SC WHERE Cno=@CNO_DEL
	DELETE FROM TC WHERE Cno=@CNO_DEL
GO

USE jiaoxuedb
GO
CREATE TRIGGER TRIGGER_UC
	ON Course FOR INSERT,UPDATE
	AS DECLARE  @CNO_NEW CHAR (2), @CNO_OLD CHAR
	SELECT @CNO_NEW=Cno FROM INSERTED
	SELECT @CNO_OLD=Cno FROM DELETED
	UPDATE SC SET Cno= @CNO_NEW WHERE Cno=@CNO_OLD
	UPDATE TC SET Cno= @CNO_NEW WHERE Cno=@CNO_OLD
GO

(2)检验触发器，自己删除或修改Course表的内容，看TC表和SC表会不会改变

4、为数据库表T 创建一触发器： 当职称从“讲师” 晋升为“ 副教授” 时，岗位津贴自动增加500 元；从“副教授” 晋升为“教授” 时，岗位津贴自动增加900 元。

(1)创建触发器：

USE jiaoxuedb
GO
CREATE TRIGGER T3
	ON Teacher FOR UPDATE
	AS    
	DECLARE @Prof_new varchar(10)	--定义新职称变量
	DECLARE @Prof_old varchar(10)   --定义旧职称变量 
	SELECT @Prof_old=Prof FROM deleted
	SELECT @Prof_new=Prof FROM inserted
	IF(@Prof_old='副教授' AND @Prof_new='教授')
		BEGIN	
			UPDATE Teacher set Comm = m.comm + 900 from Teacher m , inserted n where m.Tno = n.Tno
			PRINT'副教授晋升为教授'
			COMMIT TRAN
		END
    IF(@Prof_old='讲师' AND @Prof_new='副教授')
		BEGIN
			UPDATE Teacher set Comm = m.comm + 500 from Teacher m , inserted n where m.Tno = n.Tno
			PRINT'讲师晋升为副教授'
			COMMIT TRAN
		END
GO

(2)检验触发器：

USE jiaoxuedb
GO
UPDATE Teacher SET Prof='教授'  WHERE Tname='皇后'
GO