SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE students (student_id INT,
                       student_name VARCHAR(255), 
                       region_id INT);
CREATE TABLE regions (region_id INT, 
                      region_name VARCHAR(255),
                      students_amount INT);
INSERT INTO regions VALUES 
(1, 'Region 1', 0),
(2, 'Region 2', 0),
(3, 'Region 3', 0);
SELECT * FROM regions;


region_id
region_name
students_amount


1
Region 1
0

2
Region 2
0

3
Region 3
0


CREATE TRIGGER update_students_amount
ON students
AFTER INSERT
AS
UPDATE regions
SET regions.students_amount = data.students_amount
FROM ( SELECT students.region_id, COUNT(*) students_amount
       FROM ( SELECT DISTINCT region_id
              FROM inserted ) AS updated_regions
       JOIN students ON students.region_id = updated_regions.region_id
       GROUP BY students.region_id ) data
WHERE regions.region_id = data.region_id;
INSERT INTO students VALUES
(1, 'Student 1', 1),
(2, 'Student 2', 2),
(3, 'Student 3', 1),
(4, 'Student 4', 1);
SELECT * FROM students;
SELECT * FROM regions;


student_id
student_name
region_id


1
Student 1
1

2
Student 2
2

3
Student 3
1

4
Student 4
1




region_id
region_name
students_amount


1
Region 1
3

2
Region 2
1

3
Region 3
0


INSERT INTO students VALUES
(5, 'Student 5', 1),
(6, 'Student 6', 2),
(7, 'Student 7', 3),
(8, 'Student 8', 3);
SELECT * FROM students;
SELECT * FROM regions;


student_id
student_name
region_id


1
Student 1
1

2
Student 2
2

3
Student 3
1

4
Student 4
1

5
Student 5
1

6
Student 6
2

7
Student 7
3

8
Student 8
3




region_id
region_name
students_amount


1
Region 1
4

2
Region 2
2

3
Region 3
2

student_id	student_name	region_id
1	Student 1	1
2	Student 2	2
3	Student 3	1
4	Student 4	1
5	Student 5	1
6	Student 6	2
7	Student 7	3
8	Student 8	3