526互联

5.触发器

发布时间 2023-04-29 19:48:05作者: 郭培鑫同学

（1）、建立一个触发器xmcf，当向学生表中插入数据时，如果出现姓名重复的情况，则回滚该事务，并执行测试语句。

CREATE TRIGGER xmcf 
ON 学生表 
FOR INSERT 
AS 
BEGIN 
  SET NOCOUNT ON;
  IF EXISTS (SELECT 姓名 FROM inserted WHERE 姓名 IN (SELECT 姓名 FROM 学生表))
  BEGIN
    RAISERROR ('姓名重复！事务回滚。', 16, 1);
    ROLLBACK TRANSACTION;
    RETURN;
  END
END;
-- 插入数据，包括重复的姓名
BEGIN TRANSACTION;
INSERT INTO 学生表 VALUES (200055001,'张三','男',1988-03-09,'jsj001',870);
COMMIT;
DROP TRIGGER xmcf;

（2）、建立delete 触发器scxs ,该触发器防止删除学生表中男生的学生记录，并执行测试语句。

CREATE TRIGGER scxs 
ON 学生表 
FOR DELETE 
AS 
BEGIN 
  SET NOCOUNT ON;
  IF EXISTS (SELECT 姓名 FROM deleted WHERE 性别 = '男')
  BEGIN
    RAISERROR ('无法删除男生记录！', 16, 1);
    ROLLBACK TRANSACTION;
    RETURN;
  END
END;
--测试触发器
BEGIN TRANSACTION;
DELETE FROM 学生表 WHERE 姓名 = '郭芙蓉'; --可以删除女生记录
DELETE FROM 学生表 WHERE 姓名 = '燕小六'; --可以删除男生记录
SELECT * FROM 学生表; --检查表内容
COMMIT; --提交事务,因为事务具有一致性原则,所以以上执行内容都不会被提交
DROP TRIGGER scxs

（3）、建立一个触发器csbh,当修改教师表中的教师号时，同时修改该教师的授课记录中的教师号，并执行测试语句。

--无法创建触发器
CREATE TRIGGER csbh
ON 教师表 
FOR UPDATE 
AS 
BEGIN 
  SET NOCOUNT ON;
		IF UPDATE(教师号)
		BEGIN
		  -- 更新授课表的教师号
			UPDATE 授课表
			SET 教师号 = inserted.教师号
			FROM 授课表
			INNER JOIN inserted ON 授课表.教师号 = inserted.教师号;
			INNER JOIN deleted ON 授课表.教师号 = deleted.教师号;
  END
END;
BEGIN TRANSACTION;
UPDATE 教师表 SET 教师号 = 'js001' WHERE 教师号 = 'js010';
SELECT * FROM 授课表;
COMMIT;

（4）、创建触发器tr1_s1，在录入学生信息时同时显示表中的所有记录，并执行测试语句。

CREATE TRIGGER tr1_s1
ON 学生表
FOR INSERT
AS
BEGIN
  SET NOCOUNT ON;
  -- 显示所有学生记录
  SELECT * FROM 学生表;

  -- 执行测试语句
  PRINT 'A new student has been added.';
END;
--测试
BEGIN TRANSACTION;
INSERT INTO 学生表(学号,姓名,性别,出生日期,专业编号,入学成绩)
VALUES ('20020308','张三','男','2002-03-08','jsj001',900);
COMMIT;
DROP TRIGGER tr1_s1

（5）、在录入学生信息时同时将此学生的学号及大学英语的课程号录入到成绩表中，同时显示录入的记录信息，并执行测试语句。

CREATE TRIGGER tr2_s1
ON 学生表
FOR INSERT
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @sid INT, @cid INT;
  SELECT @sid = inserted.学号 FROM inserted;
  SELECT @cid = 课程号 FROM 课程表 WHERE 课程名 = '大学英语';

  -- 插入成绩记录
  INSERT INTO 成绩表 (学号, 课程号, 成绩)
  VALUES (@sid, @cid, 0);

  -- 显示录入的记录信息
  SELECT s.*, c.课程号 , 0 as score
  FROM 学生表 s
  INNER JOIN 课程表 c ON c.课程号 = @cid
  WHERE s.学号 = @sid;

  -- 执行测试语句
  PRINT 'A new student and a corresponding score record have been added.';
END;
--测试
BEGIN TRANSACTION;
INSERT INTO 学生表(学号,姓名,性别,出生日期,专业编号)
VALUES (200058002,'张三','男','2002-03-08','js001');
 -- 在将 nvarchar 值 'j001' 转换成数据类型 int 时失败。
COMMIT;

（6）、在删除一个毕业学生的信息同时删除这个学生的成绩记录。若没有此学生，则给出提示信息，并执行测试语句。

CREATE TRIGGER tr_DeleteStudent
ON 学生表
FOR DELETE
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @StudentID INT;
 SELECT @StudentID = deleted.学号 FROM deleted;

 IF EXISTS (SELECT * FROM 学生表 WHERE 学号 = @StudentID)
 BEGIN
    DELETE FROM 成绩表 WHERE 学号 = @StudentID;
    PRINT '已成功删除学生及其成绩记录。';
 END
 ELSE
 BEGIN
    PRINT '找不到该学生，请检查输入的ID。';
    -- 在此可以执行测试语句
 END
END
--测试
BEGIN TRANSACTION;
DELETE  FROM 学生表 WHERE 学号 = 200055001;
COMMIT;
DROP TRIGGER tr_DeleteStudent

（7）、当更新某个学生的学号时更新这个学生的选课信息，并执行测试语句。

CREATE TRIGGER tr_UpdateStudentID
ON 学生表
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @OldID INT, @NewID INT;
SELECT @OldID = deleted.学号, @NewID = inserted.学号
FROM deleted INNER JOIN inserted
ON deleted.学号 = inserted.学号;

IF @OldID <> @NewID
BEGIN
    UPDATE 成绩表 SET 学号 = @NewID
    WHERE 学号 = @OldID;
    PRINT '已成功更新选课信息。';
END
END
-- 测试
UPDATE  学生表 SET 学号 = 200055009 WHERE 学号 = 200055001;

（8）、在成绩插入到成绩表时验证是否有效，并做相应处理，执行测试语句。

-- 创建一个新的触发器
CREATE TRIGGER validate_score_trigger
ON 成绩表
AFTER INSERT, UPDATE
AS
BEGIN
  -- 如果插入或更新的成绩数据无效（小于0或大于100），将其设置为NULL值
  UPDATE 成绩表
  SET 成绩 = NULL
  WHERE 成绩 < 0 OR 成绩 > 100

  -- 检查每个学生的每个科目是否已经有了成绩记录
  UPDATE t
  SET 成绩 = i.成绩
  FROM 成绩表 t
  INNER JOIN inserted i ON t.学号 = i.学号 AND t.课程号 = i.课程号
  WHERE NOT EXISTS (
    SELECT 1 FROM 成绩表
    WHERE 学号 = t.学号
      AND 课程号 = t.课程号
  )
--测试
INSERT INTO 成绩表(学号,课程号,成绩) 
VALUES (200203081,'joo1',90),
			 (200203081,'joo1',-10);
SELECT * FROM 成绩表;   --触发器已经自动将无效的成绩数据设置为NULL值，

（9）、创建触发器以防止数据库中任何表的修改或删除操作，并执行测试语句。

-- 创建一个新的触发器
CREATE TRIGGER prevent_table_changes_trigger
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE
AS
BEGIN
  -- 抛出一个错误，防止执行删除或修改操作
  RAISERROR('Table changes are not allowed in this database.', 16, 1)
  ROLLBACK
END
DROP TABLE 课程表;-- 删了就不用上课了)_(
DROP TRIGGER prevent_table_changes_trigger ON DATABASE;

（10）、创建触发器，禁止在SQL Server服务器中删除数据库，并执行测试语句。

CREATE TRIGGER prevent_db_deletion_trigger
ON ALL SERVER
FOR DROP_DATABASE
AS
BEGIN
    -- 抛出错误，防止删除数据库
    RAISERROR('Deleting databases is not allowed on this server.', 16, 1)
    ROLLBACK
END

--测试
CREATE DATABASE my_database;
DROP DATABASE my_database;
DROP TRIGGER prevent_db_deletion_trigger ON ALL SERVER;

（11）、删除触发器tr1_s1。

DROP TRIGGER tr1_s1;