Триггер INSTEAD OF

@Traffi · Регистрация: 18.03.2013

Author24 — интернет-сервис помощи студентам

Здравствуйте! Нужна помощь...
Есть две таблицы:
Группы(НомерГруппы, Факультет, КолвоСтудентов)
Студенты(Фио,НомерГруппы,.....)

Хочу реализовать триггер INSTEAD OF UPDATE чтобы при обновлении группы у студента, данные изменения отразились на количестве групп. Из той которой перевелся в таблице КолвоСтудентов - 1, в ту которую перевелся соответственно КолвоСтудентов + 1

SQL

CREATE TRIGGER InsteadUpdate ON dbo.Студенты
INSTEAD OF UPDATE AS
BEGIN
DECLARE @insgroup CHAR(10)
DECLARE @updgroup CHAR(10)
SELECT @insgroup = НомерГруппы FROM inserted
SELECT @updgroup = НомерГруппы FROM deleted
IF EXISTS ( SELECT 
            a.НомерГруппы FROM dbo.Группы a INNER JOIN inserted b
            ON a.НомерГруппы = b.НомерГруппы
            AND a.НомерГруппы = @insgroup)
            BEGIN
            UPDATE dbo.Группы SET КолвоСтудентов = КолвоСтудентов + 1
            WHERE НомерГруппы = @insgroup
            UPDATE dbo.Группы SET КолвоСтудентов = КолвоСтудентов - 1
            WHERE НомерГруппы = @updgroup
            END
            ELSE
            RAISERROR(N'Такой группы не существует',10,10)
 
END

Ошибка с IF до BEGIN первого...всё никак не могу условие правильное сделать!

@iap · 19.03.2013, 17:32

Во-первых, почему INSTEAD OF UPDATE?
Во-вторых, как собираетесь присваивать скалярным переменным
сразу несколько значений, если проапдейтили больше одной записи одним UPDATE?
В-третьих, скобки кто считать будет?

И вообще, всё написано очень странно.

@Traffi · 19.03.2013, 17:37 **[ТС]**

Сообщение от iap

Во-первых, почему INSTEAD OF UPDATE?
Во-вторых, как собираетесь присваивать скалярным переменным
сразу несколько значений, если проапдейтили больше одной записи одним UPDATE?
В-третьих, скобки кто считать будет?

И вообще, всё написано очень странно.

Первый вопрос непонятен...

SQL

CREATE TRIGGER InsteadUpdate  ON dbo.Студенты
INSTEAD OF UPDATE AS
BEGIN
DECLARE @insgroup CHAR(10)
DECLARE @updgroup CHAR(10)
DECLARE @delname CHAR(20)
SELECT @insgroup = НомерГруппы FROM inserted
SELECT @updgroup = НомерГруппы FROM deleted
SELECT @delname = ФИО FROM deleted
IF EXISTS ( SELECT 
            a.НомерГруппы FROM dbo.Группы a INNER JOIN inserted b
            ON a.НомерГруппы = b.НомерГруппы
            AND a.НомерГруппы = @insgroup)
            BEGIN
            UPDATE dbo.Студенты SET НомерГруппы = @insgroup
            WHERE dbo.Студенты.ФИО = @delname
            UPDATE dbo.Группы SET КолвоСтудентов = КолвоСтудентов + 1
            WHERE НомерГруппы = @insgroup
            UPDATE dbo.Группы SET КолвоСтудентов = КолвоСтудентов - 1
            WHERE НомерГруппы = @updgroup
            END
            ELSE
            RAISERROR(N'Такой группы не существует',10,10)
 
END

Вот исправил, вроде работает! Я исправил ошибки о которых Вы сообщили, iap?

@iap · 19.03.2013, 20:23

Первый вопрос - почему INSTEAD OF UPDATE, а не AFTER (FOR) UPDATE? Обоснование можно увидеть?

Второе - по моему глубокому убеждению в триггере не должно быть скалярных переменных.
Для меня их наличие - признак говнокода.
Ну, разве что переменная для хранения сообщения об ошибке для финального RAISERROR()...

@Traffi · 19.03.2013, 21:47 **[ТС]**

Сообщение от iap

Первый вопрос - почему INSTEAD OF UPDATE, а не AFTER (FOR) UPDATE? Обоснование можно увидеть?

Второе - по моему глубокому убеждению в триггере не должно быть скалярных переменных.
Для меня их наличие - признак говнокода.
Ну, разве что переменная для хранения сообщения об ошибке для финального RAISERROR()...

Я новичок в этом деле! Неделю наверно как начал...
Так вот: AFTER UPDATE- я так понимаю это триггер, выполняющий действия уже после того как был совершен UPDATE. У меня же проверяется, а можно ли обновить сперва.
А почему это плохой признак? Интересно...рад любым советам

И еще вопрос. Когда тольок начинал делать, установил каскадное обновление и удаление между этими двумя таблицами. Как это можно отменить?

@cygapb-007 · 20.03.2013, 00:20

примерно так:

T-SQL

1
2
3

create table Группы(НомерГруппы int, КолвоСтудентов int)
create table Студенты(id int identity primary key, Фио varchar(50), НомерГруппы int)
go

T-SQL

create trigger Студенты_изменения on Студенты
after insert, update, delete as
-- Удаление студентов или перевод из группы
update g set g.КолвоСтудентов-=q.qty
   from Группы g
   join (select НомерГруппы, COUNT(*) qty from deleted group by НомерГруппы) q on q.НомерГруппы=g.НомерГруппы;
-- Добавление студентов или перевод в группу
merge Группы g 
   using (select НомерГруппы,count(*)qty from inserted group by НомерГруппы)q
   on  q.НомерГруппы=g.НомерГруппы
   when matched then update set g.КолвоСтудентов+=q.qty
   when not matched then insert (НомерГруппы, КолвоСтудентов) values (q.НомерГруппы, q.qty);
go

Понаблюдать за работой триггера можно в режиме пошаговой отладки:

T-SQL

insert Студенты values ('Студ1', 1)
select * from Группы
select * from Студенты
 
insert Студенты values ('Студ2', 2),('Студ3', 1)
select * from Группы
select * from Студенты
 
update Студенты set НомерГруппы=3
select * from Группы
select * from Студенты
 
delete Студенты where id<3
select * from Группы
select * from Студенты

@_katon_ · 20.03.2013, 00:28

Эм... я тоже очень интересуюсь почему переменная в триггере - говнокод?

@cygapb-007 · 20.03.2013, 00:41

триггер работает не с одиночной записью, а со всеми, подходящими по фильтру запроса. Соответственно запихивать таблицу в скаляр ошибочно.

@_katon_ · 20.03.2013, 01:18

Сообщение от cygapb-007

Соответственно запихивать таблицу в скаляр ошибочно.

не совсем понимаю как в скаляр (я так понимаю скалярная переменная) можно засунуть таблицу (я так понимаю табличный тип данных).

Сообщение от iap

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

Тут вообще речь о скалярных переменных. Я не утверждаю что это не гавнокод, но как то хотелось бы выслушать причину. А ели причины нет, то значит можно "бомбить" DECLARE в триггере без остановки...

@cygapb-007 · 20.03.2013, 01:25

Сообщение от _katon_

не совсем понимаю как в скаляр (я так понимаю скалярная переменная) можно засунуть таблицу (я так понимаю табличный тип данных).

Поясните, пожалуйста, вот этот фрагмент: что в нем делается?
Например, при выполнении запроса update студенты set НомерГруппы=35 where НомерГруппы=15 ?
Или еще круче - update студенты set НомерГруппы+=20

SQL

1 2	SELECT @insgroup = НомерГруппы FROM inserted SELECT @updgroup = НомерГруппы FROM deleted

@_katon_ · 20.03.2013, 01:36

Предположительно строковым переменным @insgroup, @updgroup задается значение поля "НомерГруппы" из табличек inserted и deleted соответственно. И в чем некашерность? То, что в данном случае это лишено смысла не означает, что в других случаях это неприменимо или вредно (никто же не может похвастаться, что видел все возможные кодирования всех возможных триггеров). Я для себя хочу установить почему это гавнокод, т.е. из каких соображений.

@cygapb-007 · 20.03.2013, 01:55

Сообщение от _katon_

Предположительно строковым переменным @insgroup, @updgroup задается значение поля "НомерГруппы" из табличек inserted и deleted соответственно. И в чем некашерность? То, что в данном случае это лишено смысла не означает, что в других случаях это неприменимо или вредно (никто же не может похвастаться, что видел все возможные кодирования всех возможных триггеров). Я для себя хочу установить почему это гавнокод, т.е. из каких соображений.

Не совсем точный ответ. Это как раз попытка втиснуть в скалярную переменную сразу все значения столбца "НомерГруппы"

В случае первого запроса у всех студентов из группы 15 меняется код группы на 35, соответственно в inserted куча строк типа Фамилия-35, а в deleted столько же строк (сколько всего было студентов в гр.15) типа Фамилия-15. В итоге после фичи типа "set @var+=field from table" в переменных будут (предположительно, не проверял) значения из какой-то одной строки этих таблиц. Для первого запроса это не критично, потому что у всех "было 15 стало 35".

Во втором запросе есть разные значения в номерах групп - и все они (кроме одного) будут проигнорированы триггером. Более того, нет гарантии, что номера групп, полученные из inserted и из deleted будут относиться к одной операции замены, а не образуется, например, пара "было 75 стало 35" (имеется в виду переход на следующий курс и соответственно "повышение" номера группы для всех студентов).

@_katon_ · 20.03.2013, 02:06

Верно, потереял я концентрацию :black_eye.:. Добавляем в эти запросы условие по id и в переменные пишется скалярное значение. Как бы сути вопроса это не меняет.

@cygapb-007 · 20.03.2013, 02:15

Сообщение от _katon_

Добавляем в эти запросы условие по id и в переменные пишется скалярное значение. Как бы сути вопроса это не меняет.

Это как? умышленно игнорируем "лишние" изменения таблицы? может проще сразу от триггера отказаться, мороки меньше?
Или, если уж очень хочется скалярных переменных, курсор в триггере организовать?

Добавлено через 3 минуты
Хотя где-то в примерах мне попадалась конструкция типа

SQL

1
2
3

IF @@rowcount=1 BEGIN
   UPDATE anothertable SET ...
END

А смысл тогда какой в таком "частично работающем" триггере?

@_katon_ · 20.03.2013, 02:25

Вы не поняли? ключевой момент интересующий меня - высказывание iap по поводу того, что скалярная переменная в триггере по его убеждению - гавнокод. Так, что меня вообще не волнует правильно ли работает триггер и какие там синтаксические или логические ошибки (да простит меня ТС). Меня интересует только почему скалярная переменная в триггере зло????? А от всего остального я абстрагируюсь.

@cygapb-007 · 20.03.2013, 02:30

Встречный вопрос)) - а для чего в триггере использовать скаляры? Какие задачи без них нерешаемы (в триггере)?

@Traffi · 20.03.2013, 05:57 **[ТС]**

[QUOTE=cygapb-007;4280145]примерно так:

T-SQL

1
2
3

create table Группы(НомерГруппы int, КолвоСтудентов int)
create table Студенты(id int identity primary key, Фио varchar(50), НомерГруппы int)
go

T-SQL

create trigger Студенты_изменения on Студенты
after insert, update, delete as
-- Удаление студентов или перевод из группы
update g set g.КолвоСтудентов-=q.qty
   from Группы g
   join (select НомерГруппы, COUNT(*) qty from deleted group by НомерГруппы) q on q.НомерГруппы=g.НомерГруппы;
-- Добавление студентов или перевод в группу
merge Группы g 
   using (select НомерГруппы,count(*)qty from inserted group by НомерГруппы)q
   on  q.НомерГруппы=g.НомерГруппы
   when matched then update set g.КолвоСтудентов+=q.qty
   when not matched then insert (НомерГруппы, КолвоСтудентов) values (q.НомерГруппы, q.qty);
go

код компактнее конечно...
Я так понял этот триггер срабатывает сразу после 3 видов обновления?
И еще один тупой вопрос!

join (select НомерГруппы, COUNT(*) qty from deleted group by НомерГруппы) q on q.НомерГруппы=g.НомерГруппы;

Зачем например тут используется group by ? До сих пор не могу понять когда и зачем ее использовать....

@cygapb-007 · 20.03.2013, 09:01

Сообщение от Traffi

Зачем например тут используется group by ? До сих пор не могу понять когда и зачем ее использовать....

Если изменения по нескольким студентам - таблица Группы будет изменена один раз для каждого из измененных номеров групп, без группировки - будет (число повторов) изменений

@iap · 20.03.2013, 09:28

Сообщение от _katon_

Предположительно строковым переменным @insgroup, @updgroup задается значение поля "НомерГруппы" из табличек inserted и deleted соответственно. И в чем некашерность? То, что в данном случае это лишено смысла не означает, что в других случаях это неприменимо или вредно (никто же не может похвастаться, что видел все возможные кодирования всех возможных триггеров). Я для себя хочу установить почему это гавнокод, т.е. из каких соображений.

Я должен объяснить свою позицию.
Триггер срабатывает не на вставляемую, удаляемую, изменяемую запись,
а на само действие - один раз на весь INSERT, один раз на весь DELETE, один раз на весь UPDATE.
Все эти команды могут затрагивать сразу несколько записей, а не одну.
Отсюда следует, что псевдотаблицы deleted и inserted могут содержать много записей.
Присваивание скалярной переменной результата запроса из этих псевдотаблиц лишены смысла в этом случае.
Ибо в переменной может быть одно значение, а реально имеем много (миллионы!!!).
Всегда, гворите, обрабатывается одна строка? И что же это гарантирует?
Ну хоть напишите проверку количества обрабатываемых записей в начале триггера!
Это будет хотя бы по-программистски.
Правильным является использование в SQL-инструкциях триггера
непосредственно deleted и inserted без всяких переменных.
Причём в предположении, что они содержат ЛЮБОЕ количество записей.

Traffi, а что касается предложенного Вами триггера INSTEAD OF UPDATE, я вспомнил, что хотел спросить Вас,
как же Вы любой UPDATE таблицы [Студенты] заменяете UPDATEом поля ДРУГОЙ таблицы [Группы]?! Где сам UPDATE студентов-то?

@cygapb-007 · 20.03.2013, 10:02

Хочу прокомментировать еще один момент в предложенном варианте триггера. В нем предполагается, что если группы нет в таблице - ее можно "молча" создать, что в реальном случае, скорее всего, будет неверно. При отсутствии номера группы в таблице, скорее всего, должна сгенерироваться ошибка и выполниться ROLLBACK TRANSACTION

@_katon_ 414 / 265 / 25 Регистрация: 03.10.2011 Сообщений: 1,079
	20.03.2013, 01:36	11
	Предположительно строковым переменным @insgroup, @updgroup задается значение поля "НомерГруппы" из табличек inserted и deleted соответственно. И в чем некашерность? То, что в данном случае это лишено смысла не означает, что в других случаях это неприменимо или вредно (никто же не может похвастаться, что видел все возможные кодирования всех возможных триггеров). Я для себя хочу установить почему это гавнокод, т.е. из каких соображений. 0

@cygapb-007 1313 / 945 / 144 Регистрация: 17.01.2013 Сообщений: 2,348
	20.03.2013, 01:55	12
	Сообщение от _katon_ Предположительно строковым переменным @insgroup, @updgroup задается значение поля "НомерГруппы" из табличек inserted и deleted соответственно. И в чем некашерность? То, что в данном случае это лишено смысла не означает, что в других случаях это неприменимо или вредно (никто же не может похвастаться, что видел все возможные кодирования всех возможных триггеров). Я для себя хочу установить почему это гавнокод, т.е. из каких соображений. Не совсем точный ответ. Это как раз попытка втиснуть в скалярную переменную сразу все значения столбца "НомерГруппы" В случае первого запроса у всех студентов из группы 15 меняется код группы на 35, соответственно в inserted куча строк типа Фамилия-35, а в deleted столько же строк (сколько всего было студентов в гр.15) типа Фамилия-15. В итоге после фичи типа "set @var+=field from table" в переменных будут (предположительно, не проверял) значения из какой-то одной строки этих таблиц. Для первого запроса это не критично, потому что у всех "было 15 стало 35". Во втором запросе есть разные значения в номерах групп - и все они (кроме одного) будут проигнорированы триггером. Более того, нет гарантии, что номера групп, полученные из inserted и из deleted будут относиться к одной операции замены, а не образуется, например, пара "было 75 стало 35" (имеется в виду переход на следующий курс и соответственно "повышение" номера группы для всех студентов). 1

@cygapb-007 1313 / 945 / 144 Регистрация: 17.01.2013 Сообщений: 2,348
	20.03.2013, 02:30	16
	Встречный вопрос)) - а для чего в триггере использовать скаляры? Какие задачи без них нерешаемы (в триггере)? 0

@iap 1116 / 761 / 183 Регистрация: 27.11.2009 Сообщений: 2,269
	19.03.2013, 17:32	2
	Во-первых, почему INSTEAD OF UPDATE? Во-вторых, как собираетесь присваивать скалярным переменным сразу несколько значений, если проапдейтили больше одной записи одним UPDATE? В-третьих, скобки кто считать будет? И вообще, всё написано очень странно. 0

@iap 1116 / 761 / 183 Регистрация: 27.11.2009 Сообщений: 2,269
	19.03.2013, 20:23	4
	Первый вопрос - почему INSTEAD OF UPDATE, а не AFTER (FOR) UPDATE? Обоснование можно увидеть? Второе - по моему глубокому убеждению в триггере не должно быть скалярных переменных. Для меня их наличие - признак говнокода. Ну, разве что переменная для хранения сообщения об ошибке для финального RAISERROR()... 0

@Traffi 1 / 1 / 0 Регистрация: 18.03.2013 Сообщений: 45
	19.03.2013, 21:47 [ТС]	5
	Сообщение от iap Первый вопрос - почему INSTEAD OF UPDATE, а не AFTER (FOR) UPDATE? Обоснование можно увидеть? Второе - по моему глубокому убеждению в триггере не должно быть скалярных переменных. Для меня их наличие - признак говнокода. Ну, разве что переменная для хранения сообщения об ошибке для финального RAISERROR()... Я новичок в этом деле! Неделю наверно как начал... Так вот: AFTER UPDATE- я так понимаю это триггер, выполняющий действия уже после того как был совершен UPDATE. У меня же проверяется, а можно ли обновить сперва. А почему это плохой признак? Интересно...рад любым советам И еще вопрос. Когда тольок начинал делать, установил каскадное обновление и удаление между этими двумя таблицами. Как это можно отменить? 0

@_katon_ 414 / 265 / 25 Регистрация: 03.10.2011 Сообщений: 1,079
	20.03.2013, 00:28	7
	Эм... я тоже очень интересуюсь почему переменная в триггере - говнокод? 0

@cygapb-007 1313 / 945 / 144 Регистрация: 17.01.2013 Сообщений: 2,348
	20.03.2013, 00:41	8
	триггер работает не с одиночной записью, а со всеми, подходящими по фильтру запроса. Соответственно запихивать таблицу в скаляр ошибочно. 0

@_katon_ 414 / 265 / 25 Регистрация: 03.10.2011 Сообщений: 1,079
	20.03.2013, 01:18	9
	Сообщение от cygapb-007 Соответственно запихивать таблицу в скаляр ошибочно. не совсем понимаю как в скаляр (я так понимаю скалярная переменная) можно засунуть таблицу (я так понимаю табличный тип данных). Сообщение от iap по моему глубокому убеждению в триггере не должно быть скалярных переменных. Тут вообще речь о скалярных переменных. Я не утверждаю что это не гавнокод, но как то хотелось бы выслушать причину. А ели причины нет, то значит можно "бомбить" DECLARE в триггере без остановки... 0

@_katon_ 414 / 265 / 25 Регистрация: 03.10.2011 Сообщений: 1,079
	20.03.2013, 02:06	13
	Верно, потереял я концентрацию :black_eye.:. Добавляем в эти запросы условие по id и в переменные пишется скалярное значение. Как бы сути вопроса это не меняет. 0

@_katon_ 414 / 265 / 25 Регистрация: 03.10.2011 Сообщений: 1,079
	20.03.2013, 02:25	15
	Вы не поняли? ключевой момент интересующий меня - высказывание iap по поводу того, что скалярная переменная в триггере по его убеждению - гавнокод. Так, что меня вообще не волнует правильно ли работает триггер и какие там синтаксические или логические ошибки (да простит меня ТС). Меня интересует только почему скалярная переменная в триггере зло????? А от всего остального я абстрагируюсь. 0

@cygapb-007 1313 / 945 / 144 Регистрация: 17.01.2013 Сообщений: 2,348
	20.03.2013, 09:01	18
	Сообщение от Traffi Зачем например тут используется group by ? До сих пор не могу понять когда и зачем ее использовать.... Если изменения по нескольким студентам - таблица Группы будет изменена один раз для каждого из измененных номеров групп, без группировки - будет (число повторов) изменений 0

@iap 1116 / 761 / 183 Регистрация: 27.11.2009 Сообщений: 2,269
	20.03.2013, 09:28	19
	Сообщение от _katon_ Предположительно строковым переменным @insgroup, @updgroup задается значение поля "НомерГруппы" из табличек inserted и deleted соответственно. И в чем некашерность? То, что в данном случае это лишено смысла не означает, что в других случаях это неприменимо или вредно (никто же не может похвастаться, что видел все возможные кодирования всех возможных триггеров). Я для себя хочу установить почему это гавнокод, т.е. из каких соображений. Я должен объяснить свою позицию. Триггер срабатывает не на вставляемую, удаляемую, изменяемую запись, а на само действие - один раз на весь INSERT, один раз на весь DELETE, один раз на весь UPDATE. Все эти команды могут затрагивать сразу несколько записей, а не одну. Отсюда следует, что псевдотаблицы deleted и inserted могут содержать много записей. Присваивание скалярной переменной результата запроса из этих псевдотаблиц лишены смысла в этом случае. Ибо в переменной может быть одно значение, а реально имеем много (миллионы!!!). Всегда, гворите, обрабатывается одна строка? И что же это гарантирует? Ну хоть напишите проверку количества обрабатываемых записей в начале триггера! Это будет хотя бы по-программистски. Правильным является использование в SQL-инструкциях триггера непосредственно deleted и inserted без всяких переменных. Причём в предположении, что они содержат ЛЮБОЕ количество записей. Traffi, а что касается предложенного Вами триггера INSTEAD OF UPDATE, я вспомнил, что хотел спросить Вас, как же Вы любой UPDATE таблицы [Студенты] заменяете UPDATEом поля ДРУГОЙ таблицы [Группы]?! Где сам UPDATE студентов-то? 2

@cygapb-007 1313 / 945 / 144 Регистрация: 17.01.2013 Сообщений: 2,348
	20.03.2013, 10:02	20
	Хочу прокомментировать еще один момент в предложенном варианте триггера. В нем предполагается, что если группы нет в таблице - ее можно "молча" создать, что в реальном случае, скорее всего, будет неверно. При отсутствии номера группы в таблице, скорее всего, должна сгенерироваться ошибка и выполниться ROLLBACK TRANSACTION 0