Создать триггер в SQL Server Management Studio

Question

Препод задал сделать на паре (как доп. задание) триггер, который считает кол-во студентов по регионам и записывает это в другую таблицу. Я создал представление, которое показывает, какой примерно вывод мы получим в той таблице:

SELECT        dbo.Regions.Region, COUNT(dbo.Students.ID) AS Expr1
FROM            dbo.Students INNER JOIN
                         dbo.Regions ON dbo.Students.Region = dbo.Regions.ID
GROUP BY dbo.Regions.Region, dbo.Students.Region

Вывод:
Белгородская область 3
Тверская область 2

Триггер должен работать на основе таблицы Students. Листинг таблицы:

CREATE TABLE [dbo].[Students](
    [ID] [int] NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
    [DateBorn] [date] NOT NULL,
    [Region] [int] NOT NULL,
    [Nationality] [int] NOT NULL,
    [Group] [int] NOT NULL,
 CONSTRAINT [PK_Students] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Students]  WITH CHECK ADD  CONSTRAINT [FK_Students_Groups] FOREIGN KEY([Group])
REFERENCES [dbo].[Groups] ([ID])
GO

ALTER TABLE [dbo].[Students] CHECK CONSTRAINT [FK_Students_Groups]
GO

ALTER TABLE [dbo].[Students]  WITH CHECK ADD  CONSTRAINT [FK_Students_Nationalities] FOREIGN KEY([Nationality])
REFERENCES [dbo].[Nationalities] ([ID])
GO

ALTER TABLE [dbo].[Students] CHECK CONSTRAINT [FK_Students_Nationalities]
GO

ALTER TABLE [dbo].[Students]  WITH CHECK ADD  CONSTRAINT [FK_Students_Regions] FOREIGN KEY([Region])
REFERENCES [dbo].[Regions] ([ID])
GO

ALTER TABLE [dbo].[Students] CHECK CONSTRAINT [FK_Students_Regions]
GO

Листинг таблицы Regions:

CREATE TABLE [dbo].[Regions](
    [ID] [int] NOT NULL,
    [Region] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Regions] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

Answer 1

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;

DEMO fiddle

БЛОГ НА HUSL

Создать триггер в SQL Server Management Studio

Ответы (1 шт):