В чем ошибка триггера?

Question

[kirillwwin]

Две таблицы: таблица с расписанием уроков у студентов и таблица с оценками студентов по предметам.
Задание: нужно написать триггер не позволяющий ставить оценки студентам по предметам которые студент не изучает.

spoiler

alter trigger studyschema.prog_grup
on studyschema.progress
after insert, update
as
declare @grupp varchar (20)
select @grupp = grup_id
from StudySchema.Progress
if @grupp not in (select grup_id from StudySchema.Study group by Grup_ID)
begin
rollback tran raiserror ('Количество оценок не может привешать количество предметов',16,10)
end;

Answer 1

[Константин Цветков]

Делать триггер INSTEAD OF, то есть до (вместо) операции.
Проверять все параметры.
Не забывать, что нет FOR EACH ROW.

Тестовые данные

USE Testus
GO
DROP TABLE IF EXISTS Study
CREATE TABLE Study ( Grup_ID INT, Subj_ID INT )
INSERT Study VALUES
( 1, 1 ), ( 1, 2 ), ( 1, 3 ), ( 1, 5 ),
( 2, 2 ), ( 2, 3 ), ( 2, 4 ), ( 3, 5 ),
( 3, 7 ) 
DROP TABLE IF EXISTS Progress
CREATE TABLE Progress ( Stud_ID INT, Grup_ID INT, Subj_ID INT, [Оценка] INT)
INSERT Progress VALUES
( 1, 1, 1, 3 ), ( 1, 1, 2, 5 ), ( 1, 1, 3, 5 ),
( 2, 1, 1, 4 ), ( 2, 1, 2, 5 ), ( 2, 1, 3, 2 ),
( 1, 2, 1, 3 ), ( 1, 2, 2, 5 ), ( 1, 2, 3, 5 ),
( 2, 2, 1, 4 ), ( 2, 2, 2, 5 ), ( 2, 2, 3, 2 ),
( 1, 3, 1, 3 ), ( 1, 3, 2, 5 ), ( 1, 3, 7, 5 ),
( 2, 3, 1, 4 ), ( 2, 3, 2, 5 ), ( 2, 3, 7, 2 )
-- Проверка все ли оценки возможны
SELECT Progress.Stud_ID, Study.Grup_ID, Study.Subj_ID, [Оценка]
  FROM Progress 
    LEFT OUTER JOIN Study 
      ON Progress.Grup_ID = Study.Grup_ID AND Progress.Subj_ID = Study.Subj_ID

Без триггера:

DROP TABLE IF EXISTS ProgressClear
CREATE TABLE ProgressClear ( Stud_ID INT, Grup_ID INT, Subj_ID INT, [Оценка] INT)
INSERT ProgressClear
  SELECT Progress.Stud_ID, Study.Grup_ID, Study.Subj_ID, [Оценка]
    FROM Progress 
      INNER JOIN Study 
        ON Progress.Grup_ID = Study.Grup_ID AND Progress.Subj_ID = Study.Subj_ID
SELECT * FROM ProgressClear

Результат

Stud_ID	Grup_ID	Subj_ID	Оценка
1	1	1	3
1	1	2	5
1	1	3	5
2	1	1	4
2	1	2	5
2	1	3	2
1	2	2	5
1	2	3	5
2	2	2	5
2	2	3	2
1	3	7	5
2	3	7	2

Триггер:

DROP TABLE IF EXISTS ProgressClear
CREATE TABLE ProgressClear ( Stud_ID INT, Grup_ID INT, Subj_ID INT, [Оценка] INT)
GO
CREATE TRIGGER OnlyPure 
  ON ProgressClear INSTEAD OF INSERT
AS
DECLARE @RC INT
SELECT @RC = COUNT(*) FROM inserted
INSERT ProgressClear
  SELECT inserted.*
    FROM inserted 
      INNER JOIN Study 
        ON inserted.Grup_ID = Study.Grup_ID AND inserted.Subj_ID = Study.Subj_ID
IF @@ROWCOUNT < @RC RAISERROR( 'Оценка не соответствует учебному плану.', 16, 10 )
GO
INSERT ProgressClear
  SELECT * FROM Progress

SELECT * FROM ProgressClear

Результат тот же.

Comments

[kirillwwin]

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

alter trigger studyschema.secon
on studyschema.progress
after insert, update
as
declare @subj2 varchar (20)
declare @subj varchar(30)
declare @ocenka varchar(10)
select @ocenka = ocenka 
from inserted 
select @subj = p.grup_id + p.subj_ID + p.Teach_ID, @subj2 = s.grup_id + s.Subj_ID + s.Teach_ID
from StudySchema.Progress p inner join StudySchema.study s on p.Subj_ID=s.Subj_ID inner join inserted i on p.OCENKA=i.OCENKA
if @subj <> @subj2
begin
rollback tran raiserror ('Количество оценок не может привешать количество предметов',16,10)
end;

[kirillwwin]

Мне такой формат не подходи мне нельзя изменять таблицы, нужно сделать так как я показал в прошлом комментарии через условие if

[Константин Цветков]

kirillwwin, Вам не нужно менять таблицу, сделаете два триггера на добавление и изменение.

[kirillwwin]

в вашем примеру сверху я вижу только один триггер на добавление

[Константин Цветков]

kirillwwin, Триггер на изменение аналогичен добавлению.
Добавил IF с прерыванием ошибкой.

[Константин Цветков]

kirillwwin, Пардон, забыл посчитать количество входных данных.