创建触发器当增加、删除教职工和修改教职工部门信息时自动修改相应部门的职工人数
时间: 2023-11-09 14:06:40 浏览: 248
可以按照以下步骤创建触发器:
1. 首先,需要创建一个存储过程,用于更新部门职工人数。可以使用类似以下的代码:
```
CREATE PROCEDURE UpdateDepartmentEmployeeCount
AS
BEGIN
UPDATE Department
SET EmployeeCount = (SELECT COUNT(*) FROM Employee WHERE DepartmentId = Department.Id)
END
```
2. 接下来,创建一个名为“EmployeeTrigger”的触发器,用于在增加、删除或修改教职工信息时自动更新部门职工人数。可以使用类似以下的代码:
```
CREATE TRIGGER EmployeeTrigger ON Employee
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
EXEC UpdateDepartmentEmployeeCount
END
```
3. 最后,将触发器与部门表相关联。可以使用以下代码:
```
ALTER TABLE Department
ADD CONSTRAINT FK_Department_EmployeeTrigger
FOREIGN KEY(EmployeeCount)
REFERENCES EmployeeTrigger
```
这样,当教职工信息发生变化时,触发器会自动调用存储过程更新相应部门的职工人数。
相关问题
请用sql语言写出下列要求。学校人力资源管理系统 实现学校部门信息,职务、职称和教职工信息管理; 实现教师的学籍经历管理; 实现教师的家庭关系管理; 实现教师的奖惩信息管理; 创建存储过程查询学校各部门各种职称的教职工数量; 创建触发器,当增加、删除教职工和修改教职工部门信息时自动修改相应部门的职工人数; 创建规则用于保证教职工的E-Mail的输入格式正确; 建立数据库相关表之间的参照完整性约束。
下面是针对学校人力资源管理系统的SQL语句:
1.创建部门信息表
```sql
CREATE TABLE department (
dep_id INT PRIMARY KEY,
dep_name VARCHAR(50) NOT NULL,
dep_manager VARCHAR(50) NOT NULL
);
```
2.创建职务信息表
```sql
CREATE TABLE position (
pos_id INT PRIMARY KEY,
pos_name VARCHAR(50) NOT NULL
);
```
3.创建职称信息表
```sql
CREATE TABLE title (
title_id INT PRIMARY KEY,
title_name VARCHAR(50) NOT NULL
);
```
4.创建教职工信息表
```sql
CREATE TABLE teacher (
teacher_id INT PRIMARY KEY,
teacher_name VARCHAR(50) NOT NULL,
teacher_email VARCHAR(50) NOT NULL,
teacher_dep_id INT NOT NULL,
teacher_pos_id INT NOT NULL,
teacher_title_id INT NOT NULL,
FOREIGN KEY (teacher_dep_id) REFERENCES department(dep_id),
FOREIGN KEY (teacher_pos_id) REFERENCES position(pos_id),
FOREIGN KEY (teacher_title_id) REFERENCES title(title_id)
);
```
5.创建学籍经历信息表
```sql
CREATE TABLE academic (
academic_id INT PRIMARY KEY,
academic_name VARCHAR(50) NOT NULL,
academic_start_date DATE NOT NULL,
academic_end_date DATE NOT NULL,
teacher_id INT NOT NULL,
FOREIGN KEY (teacher_id) REFERENCES teacher(teacher_id)
);
```
6.创建家庭关系信息表
```sql
CREATE TABLE family (
family_id INT PRIMARY KEY,
family_name VARCHAR(50) NOT NULL,
family_relation VARCHAR(50) NOT NULL,
teacher_id INT NOT NULL,
FOREIGN KEY (teacher_id) REFERENCES teacher(teacher_id)
);
```
7.创建奖惩信息表
```sql
CREATE TABLE reward_punish (
rp_id INT PRIMARY KEY,
rp_content VARCHAR(50) NOT NULL,
rp_date DATE NOT NULL,
teacher_id INT NOT NULL,
FOREIGN KEY (teacher_id) REFERENCES teacher(teacher_id)
);
```
8.创建存储过程查询学校各部门各种职称的教职工数量
```sql
CREATE PROCEDURE teacher_count()
BEGIN
SELECT dep_name, pos_name, title_name, COUNT(teacher_id)
FROM teacher
INNER JOIN department ON teacher_dep_id = dep_id
INNER JOIN position ON teacher_pos_id = pos_id
INNER JOIN title ON teacher_title_id = title_id
GROUP BY dep_name, pos_name, title_name;
END;
```
9.创建触发器,当增加、删除教职工和修改教职工部门信息时自动修改相应部门的职工人数
```sql
CREATE TRIGGER teacher_count_update
AFTER INSERT ON teacher
FOR EACH ROW
BEGIN
UPDATE department
SET dep_manager = dep_manager,
COUNT = COUNT + 1
WHERE dep_id = NEW.teacher_dep_id;
END;
CREATE TRIGGER teacher_count_delete
AFTER DELETE ON teacher
FOR EACH ROW
BEGIN
UPDATE department
SET dep_manager = dep_manager,
COUNT = COUNT - 1
WHERE dep_id = OLD.teacher_dep_id;
END;
CREATE TRIGGER teacher_count_update
AFTER UPDATE ON teacher
FOR EACH ROW
BEGIN
UPDATE department
SET dep_manager = dep_manager,
COUNT = COUNT + 1
WHERE dep_id = NEW.teacher_dep_id;
UPDATE department
SET dep_manager = dep_manager,
COUNT = COUNT - 1
WHERE dep_id = OLD.teacher_dep_id;
END;
```
10.创建规则用于保证教职工的E-Mail的输入格式正确
```sql
CREATE RULE email_rule AS
ON INSERT TO teacher
WHERE NOT (teacher_email LIKE '%@%.%')
DO INSTEAD
RAISE EXCEPTION 'Invalid email format';
```
11.建立数据库相关表之间的参照完整性约束
```sql
ALTER TABLE teacher
ADD FOREIGN KEY (teacher_dep_id) REFERENCES department(dep_id) ON DELETE CASCADE;
ALTER TABLE teacher
ADD FOREIGN KEY (teacher_pos_id) REFERENCES position(pos_id) ON DELETE CASCADE;
ALTER TABLE teacher
ADD FOREIGN KEY (teacher_title_id) REFERENCES title(title_id) ON DELETE CASCADE;
ALTER TABLE academic
ADD FOREIGN KEY (teacher_id) REFERENCES teacher(teacher_id) ON DELETE CASCADE;
ALTER TABLE family
ADD FOREIGN KEY (teacher_id) REFERENCES teacher(teacher_id) ON DELETE CASCADE;
ALTER TABLE reward_punish
ADD FOREIGN KEY (teacher_id) REFERENCES teacher(teacher_id) ON DELETE CASCADE;
```
写一段数据库代码当增加、删除教职工和修改教职工部门信息时自动修改相应部门的职工人数;
假设有两个表,一个是教职工信息表,另一个是部门信息表,它们之间是一对多的关系,即一个部门可以有多个教职工。在这种情况下,可以编写一个触发器来实现当增加、删除或修改教职工时自动更新相应部门的职工人数。具体代码如下:
```
CREATE OR REPLACE TRIGGER trg_update_dept_emp_count
AFTER INSERT OR UPDATE OR DELETE ON tb_employee
FOR EACH ROW
DECLARE
v_dept_id tb_department.dept_id%TYPE;
BEGIN
-- 获取教职工所在部门ID
SELECT dept_id INTO v_dept_id FROM tb_department WHERE dept_id = :new.dept_id;
-- 更新部门的职工人数
UPDATE tb_department SET emp_count = (SELECT COUNT(*) FROM tb_employee WHERE dept_id = v_dept_id) WHERE dept_id = v_dept_id;
END;
/
```
这个触发器会在每次插入、更新或删除教职工信息时触发。它会根据教职工所在的部门ID来更新相应部门的职工人数。具体做法是,首先获取教职工所在部门的ID,然后使用子查询来计算该部门的职工人数,最后将计算得到的人数更新到部门信息表中。
需要注意的是,这里假设教职工信息表中有一个dept_id字段,用于记录教职工所在的部门ID;部门信息表中有一个dept_id字段和一个emp_count字段,分别用于记录部门ID和职工人数。如果您的表结构不同,需要根据实际情况进行修改。
阅读全文