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