写出测试CREATE TRIGGER tr_stu_dept ON student AFTER INSERT, UPDATE AS IF EXISTS (SELECT * FROM INSERTED WHERE sdept = '计算机系' OR sdept !='信息管理系' OR sdept !='数学系' OR sdept !='通信工程系') ROLLBACK 的执行情况的代码
时间: 2023-11-30 10:03:39 浏览: 63
假设我们有以下的 `student` 表:
```sql
CREATE TABLE student (
sid INT PRIMARY KEY,
sname VARCHAR(20),
sdept VARCHAR(20)
);
```
下面是创建触发器并测试的代码:
```sql
-- 创建触发器
CREATE TRIGGER tr_stu_dept ON student AFTER INSERT, UPDATE AS
IF EXISTS (
SELECT * FROM INSERTED
WHERE sdept = '计算机系' OR sdept != '信息管理系' OR sdept != '数学系' OR sdept != '通信工程系'
)
BEGIN
RAISERROR('Invalid department', 16, 1)
ROLLBACK
END;
GO
-- 插入一条合法的记录
INSERT INTO student (sid, sname, sdept) VALUES (1, 'Alice', '计算机系')
-- 插入一条非法的记录
INSERT INTO student (sid, sname, sdept) VALUES (2, 'Bob', '地理系')
```
执行结果:
```
(1 row affected)
Msg 50000, Level 16, State 1, Procedure tr_stu_dept, Line 4 [Batch Start Line 20]
Invalid department
Msg 3609, Level 16, State 1, Line 2
The transaction ended in the trigger. The batch has been aborted.
```
可以看到,第一次插入的记录符合条件,未触发触发器,成功插入。第二次插入的记录不符合条件,触发器抛出错误并回滚事务,导致插入失败。
阅读全文