(1) 编写一个触发器,保证employee表中的job_lvl的值在jobs表的相应记录的min_lvl和max_lvl范围内。
时间: 2023-07-15 12:09:57 浏览: 82
可以使用触发器来实现该功能,具体步骤如下:
1. 创建一个新的触发器:
```sql
CREATE TRIGGER trg_employee_job_lvl
BEFORE INSERT OR UPDATE ON employee
FOR EACH ROW
```
2. 在触发器中添加条件判断,判断employee表中的job_lvl是否在jobs表中相应记录的min_lvl和max_lvl范围内:
```sql
IF NEW.job_lvl NOT BETWEEN (SELECT min_lvl FROM jobs WHERE job_id = NEW.job_id) AND (SELECT max_lvl FROM jobs WHERE job_id = NEW.job_id) THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Job level is out of range for selected job';
END IF;
```
3. 如果job_lvl不在范围内,则使用SIGNAL语句抛出一个异常。
完整的SQL代码如下:
```sql
DELIMITER $$
CREATE TRIGGER trg_employee_job_lvl
BEFORE INSERT OR UPDATE ON employee
FOR EACH ROW
BEGIN
IF NEW.job_lvl NOT BETWEEN (SELECT min_lvl FROM jobs WHERE job_id = NEW.job_id) AND (SELECT max_lvl FROM jobs WHERE job_id = NEW.job_id) THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Job level is out of range for selected job';
END IF;
END$$
DELIMITER ;
```
注意:该触发器只能检查新插入或更新的记录,如果jobs表中的min_lvl或max_lvl被修改,可能会导致employee表中的记录不再符合要求。此时需要手动检查并更新employee表中的数据。