数据库原理与应用实验报告十五

的状态。)

? 插入数据行('s5','c1','101'),是否弹出如图所示的错误。请问该行数据是否插入到sc表中

? 将s5的c1成绩更新至101,能否更新成功?

USE jxsk

IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE NAME='SCORE_SC_TRI' AND TYPE='TR')

DROP TRIGGER SCORE_SC_TRI GO

CREATE TRIGGER SCORE_SC_TRI ON SC FOR INSERT, UPDATE AS

DECLARE @SCORE_IN TINYINT

SELECT @SCORE_IN=SCORE FROM INSERTED IF @SCORE_IN<0 OR @SCORE_IN>100 BEGIN

PRINT '成绩超出0-100!请重新输入.' ROLLBACK TRANSACTION END GO

INSERT INTO SC VALUES ('s5','c1','101') /*触发器激活,插入失败*/

UPDATE SC SET SCORE=101 WHERE SNO='s5' AND CNO='c1' 激活,更新失败*/

/*触发器

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

更新前:

更新后:

USE jxsk

IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE NAME='UT' AND TYPE='TR')

DROP TRIGGER UT GO

CREATE TRIGGER UT ON T FOR UPDATE AS

DECLARE @PROF_OLD CHAR(10) DECLARE @PROF_NEW CHAR(10) DECLARE @TNO CHAR(2)

SELECT @PROF_OLD=PROF FROM DELETED SELECT @PROF_NEW=PROF FROM INSERTED SELECT @TNO=TNO FROM DELETED

IF @PROF_OLD='讲师' AND @PROF_NEW='副教授' BEGIN

UPDATE T SET COMM=COMM+500 WHERE TNO=@TNO

END

IF @PROF_OLD='副教授' AND @PROF_NEW='教授' BEGIN

UPDATE T SET COMM=COMM+900 WHERE TNO=@TNO END GO

UPDATE T SET PROF='副教授' WHERE TN='刘伟'

联系客服:779662525#qq.com(#替换为@) 苏ICP备20003344号-4