employee的字段有emp_id、name、gender、tel、id、job_id、job_lvl、hire_date; job表中的字段有job_id、job_desc、min_lvl、max_lvl; employee_log表中的字段有emp_id、name、gender、tel、id、job_id、job_lvl、hire_date、quit_date;使用sql编写一个触发器,保证employee表中的job_lvl的值在jobs表的相应记录的min_lvl和max_lvl范围内。并验证
时间: 2023-06-26 22:09:44 浏览: 69
employee 表数据
可以使用以下SQL代码创建一个触发器,每当在employee表中插入、更新或删除数据时,都会自动检查job_lvl的值是否在jobs表中的min_lvl和max_lvl范围内:
```sql
CREATE TRIGGER check_job_lvl
BEFORE INSERT OR UPDATE OR DELETE ON employee
FOR EACH ROW
BEGIN
IF (NEW.job_lvl < (SELECT min_lvl FROM jobs WHERE job_id = NEW.job_id) OR
NEW.job_lvl > (SELECT max_lvl FROM jobs WHERE job_id = NEW.job_id)) THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'job_lvl value out of range';
END IF;
END;
```
这个触发器会在每次插入、更新或删除employee表中的数据时执行。它会检查新插入或更新的行的job_lvl值是否在jobs表中对应的记录的min_lvl和max_lvl范围内。如果不是,就会抛出一个错误。同样的,如果删除的记录的job_lvl值不在范围内,也会抛出错误。
可以通过以下测试用例验证触发器的正确性:
首先,创建一个名为jobs的表,并添加一些数据:
```sql
CREATE TABLE jobs (
job_id INT PRIMARY KEY,
job_desc VARCHAR(50),
min_lvl INT,
max_lvl INT
);
INSERT INTO jobs VALUES (1, 'Manager', 1, 5);
INSERT INTO jobs VALUES (2, 'Clerk', 1, 3);
INSERT INTO jobs VALUES (3, 'Programmer', 3, 7);
```
然后,创建一个名为employee的表,并添加一些数据:
```sql
CREATE TABLE employee (
emp_id INT PRIMARY KEY,
name VARCHAR(50),
gender VARCHAR(10),
tel VARCHAR(20),
id VARCHAR(20),
job_id INT,
job_lvl INT,
hire_date DATE
);
INSERT INTO employee VALUES (1, 'Alice', 'Female', '123456789', '111111111111111111', 1, 4, '2022-01-01');
INSERT INTO employee VALUES (2, 'Bob', 'Male', '987654321', '222222222222222222', 2, 2, '2022-01-01');
```
接下来,插入一条符合范围的数据:
```sql
INSERT INTO employee VALUES (3, 'Charlie', 'Male', '555555555', '333333333333333333', 3, 4, '2022-01-01');
-- 这条语句应该成功执行
```
然后,插入一条不符合范围的数据:
```sql
INSERT INTO employee VALUES (4, 'David', 'Male', '666666666', '444444444444444444', 3, 8, '2022-01-01');
-- 这条语句应该抛出一个错误
```
最后,更新一条数据,使其不符合范围:
```sql
UPDATE employee SET job_lvl = 2 WHERE emp_id = 1;
-- 这条语句应该抛出一个错误
```
通过以上测试用例,可以验证触发器的正确性。
阅读全文