Вычисляемое поле одной таблицы по количеству записей в другой: триггер?

@NewOrdered · Регистрация: 25.05.2012

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

Добрый день!
Вопрос начинающего: необходимо, чтобы поле Tours.Capacity автоматически вычислялось по количеству записей в Tourists-Tours соответствующего TourID (см. вложение). Пока что решил задачу в лоб триггерами AFTER INSERT, AFTER DELETE:

SQL

CREATE TRIGGER [dbo].[Tourists-Tours_tri] ON [dbo].[Tourists-Tours]
WITH EXECUTE AS CALLER
FOR INSERT, UPDATE
AS
BEGIN
  /* Trigger body */
  DECLARE @a AS INTEGER;
  DECLARE @b AS INTEGER;
  SET @b = (SELECT TourID FROM inserted);
  SET @a = (SELECT COUNT (TourID) FROM [dbo].[Tourists-Tours] WHERE TourID = @b);
 
  UPDATE Tours SET Capacity = @a WHERE TourID = @b; 
END

Может есть более изящное/правильно решение?

Или я пытаюсь изобрести велосипед и достаточно будет обойтись JOIN для вывода количества?

@iap · 18.07.2013, 20:08

Можно в [dbo].[Tourists-Tours] одним INSERTом вставить 2 записи?

@invm · 18.07.2013, 22:54

Индексированное представление позволит обойтись без тиггера(ов). Тем более, что он неправильный.
1.

T-SQL

1	alter table Tours drop column Capacity

2.

T-SQL

create view vTourCapacity
with schemabinding
as
select
 TourID,
 count_big(*) as Capacity
from
 dbo.[Tourists-Tours]
group by
 TourID;
go
 
create unique clustered index IX_vTourCapacity__TourID on dbo.vTourCapacity (TourID);

@NewOrdered · 19.07.2013, 10:09 **[ТС]**

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

Можно в [dbo].[Tourists-Tours] одним INSERTом вставить 2 записи?

Нет, один INSERT - одна запись.
Но это искусственное ограничение в силу моих недостаточных знаний в этой области. Если можно будет обойтись без триггеров или реализовать обработку вставки нескольких записей, то конечно же его не будет.

Добавлено через 11 минут

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

Индексированное представление позволит обойтись без тиггера(ов). Тем более, что он неправильный.
1.

T-SQL

1	alter table Tours drop column Capacity

2.

T-SQL

create view vTourCapacity
with schemabinding
as
select
 TourID,
 count_big(*) as Capacity
from
 dbo.[Tourists-Tours]
group by
 TourID;
go
 
create unique clustered index IX_vTourCapacity__TourID on dbo.vTourCapacity (TourID);

Спасибо, работает как требовалось

@iap · 19.07.2013, 10:18

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

Нет, один INSERT - одна запись.
Но это искусственное ограничение в силу моих недостаточных знаний в этой области. Если можно будет обойтись без триггеров или реализовать обработку вставки нескольких записей, то конечно же его не будет.)

Где это ограничение??
Кто помешает мне выполнить INSERT двух записей?
Вы проверяете на входе в триггер, что записей не больше одной? Нет!
Типичный пример говнокода, который работает только в очень специальных случаях
и с грохотом падает при малейшем отклонении от них.

@NewOrdered · 19.07.2013, 11:35 **[ТС]**

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

Где это ограничение??
Кто помешает мне выполнить INSERT двух записей?
Вы проверяете на входе в триггер, что записей не больше одной? Нет!
Типичный пример говнокода, который работает только в очень специальных случаях
и с грохотом падает при малейшем отклонении от них.

Вам - никто. А пользователю, работающему с формой, которая добавляет записи, - я как разработчик этой формы. И если потребуется, то будут реализованы проверки/ограничения на стороне сервера.
И почему вы решили, что это будет окончательный вариант решения задачи?
Пока что это только дискуссия.

Добавлено через 8 минут
iap, Возможно, это действительно неправильный подход - создавать код для "специальных случаев". Но я для того и здесь, чтобы понять, где я ошибаюсь.

@iap · 19.07.2013, 11:41

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

iap, Возможно, это действительно неправильный подход - создавать код для "специальных случаев". Но я для того и здесь, чтобы понять, где я ошибаюсь.

Дык, я и показываю, где.
Может, не всё показываю, но я же не один на форуме?

В общем, форма какая-то тут совершенно не при чём.
Клиент-серверная технология - это в общем случае один сервер и сколько угодно клиентов,
которые должны посылать серверу корректные запросы, а он их должен корректно обработать.
Ваш клиент вставляет по одной строке, а другой вставляет сразу миллион!
И это нормальная ситуация.

@NewOrdered · 19.07.2013, 13:09 **[ТС]**

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

Дык, я и показываю, где.
Может, не всё показываю, но я же не один на форуме?

В общем, форма какая-то тут совершенно не при чём.
Клиент-серверная технология - это в общем случае один сервер и сколько угодно клиентов,
которые должны посылать серверу корректные запросы, а он их должен корректно обработать.
Ваш клиент вставляет по одной строке, а другой вставляет сразу миллион!
И это нормальная ситуация.

В любом случае, спасибо за указание на грабли

Добавлено через 1 час 7 минут

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

Индексированное представление позволит обойтись без тиггера(ов). Тем более, что он неправильный.
1.

T-SQL

1	alter table Tours drop column Capacity

2.

T-SQL

create view vTourCapacity
with schemabinding
as
select
 TourID,
 count_big(*) as Capacity
from
 dbo.[Tourists-Tours]
group by
 TourID;
go
 
create unique clustered index IX_vTourCapacity__TourID on dbo.vTourCapacity (TourID);

Правильно ли будет делать вывод Capacity вот так:

T-SQL

SELECT 
T.TourID as 'N', 
TL.Name as 'Наименование', 
T.DateStart as 'Дата отъезда', 
T.DateEnd as 'Дата приезда', 
TC.Capacity as 'Кол-во туристов',
T.Comment as 'Комментарий'
FROM Tours as T 
JOIN TourList as TL ON TL.TourListID = T.TourListID
LEFT JOIN vTourCapacity as TC ON TC.TourID = T.TourID

?

@invm · 19.07.2013, 15:23

Вот так:

T-SQL

1
2
3

...
LEFT JOIN vTourCapacity with (noexpand) as TC ON TC.TourID = T.TourID
...

@cygapb-007 · 19.07.2013, 16:15

invm, а почему LEFT JOIN а не INNER JOIN ? Это же scan вместо seek ? А TourID во view присутствуют все, и однократно...

@invm · 19.07.2013, 16:52

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

Это же scan вместо seek

Почему вы так решили?

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

А TourID во view присутствуют все

В том-то и дело, что не все, а только те для которых есть записи в [Tourists-Tours].

@cygapb-007 · 19.07.2013, 18:28

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

Почему вы так решили?

в план заглянул

Никогда не пользовался индексированными представлениями - интересно стало))

Кликните здесь для просмотра всего текста

T-SQL

create table Tours (TourID int identity primary key, name nvarchar(100))
insert Tours values ('t1'),('t2')
 
create table [Tourists-Tours] (TourID int, TouristID int, primary key(TourID, TouristID))
go
create view vTourCapacity with schemabinding as
select TourID, count_big(*) as Capacity
   from dbo.[Tourists-Tours]
   group by TourID;
go
create unique clustered index IX_vTourCapacity__TourID on dbo.vTourCapacity (TourID);
go
 
insert [Tourists-Tours] values (1,1)
insert [Tourists-Tours] values (1,2),(2,2)
insert [Tourists-Tours] values (1,1)
update [Tourists-Tours] set TouristID=3 where TourID=1 and TouristID=2
delete [Tourists-Tours] where TourID=2 and TouristID=2
 
select * 
from Tours t
join [Tourists-Tours] l on l.TourID=t.TourID
join vTourCapacity v on v.TourID=t.TourID
 
select * 
from Tours t
join [Tourists-Tours] l on l.TourID=t.TourID
join vTourCapacity v with(noexpand) on v.TourID=t.TourID
 
select * 
from Tours t
join [Tourists-Tours] l on l.TourID=t.TourID
left join vTourCapacity v on v.TourID=t.TourID
 
select * 
from Tours t
join [Tourists-Tours] l on l.TourID=t.TourID
left join vTourCapacity v with(noexpand) on v.TourID=t.TourID
 
select * from Tours
select * from [Tourists-Tours]
select * from vTourCapacity
 
drop table Tours
drop view vTourCapacity
drop table [Tourists-Tours]

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

В том-то и дело, что не все, а только те для которых есть записи в [Tourists-Tours].

Да, протормозил...

Добавлено через 48 минут
С другой стороны, ТС указал INNER JOIN TourList, значит и vTourCapacity нужно тоже по INNER JOIN подключать...

Добавлено через 2 минуты
а еще точнее,

T-SQL

select * 
from Tours t
left join [Tourists-Tours] l 
   join vTourCapacity v with(noexpand) on v.TourID=l.TourID
   on l.TourID=t.TourID

@invm · 20.07.2013, 16:01

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

в план заглянул

Ну план разный может быть. При lef join будет merge и 2 scan'а. А вот при inner может быть, в зависимости от кардинальности, либо скан по vTourCapacity и seek по Tours + Nested Loops, либо merge и 2 scan'а.
Но ТС'у-то нужен left...

@iap 1116 / 761 / 183 Регистрация: 27.11.2009 Сообщений: 2,269
	18.07.2013, 20:08	2
	Можно в [dbo].[Tourists-Tours] одним INSERTом вставить 2 записи? 0

@iap 1116 / 761 / 183 Регистрация: 27.11.2009 Сообщений: 2,269
	19.07.2013, 10:18	5
	Сообщение от NewOrdered Нет, один INSERT - одна запись. Но это искусственное ограничение в силу моих недостаточных знаний в этой области. Если можно будет обойтись без триггеров или реализовать обработку вставки нескольких записей, то конечно же его не будет.) Где это ограничение?? Кто помешает мне выполнить INSERT двух записей? Вы проверяете на входе в триггер, что записей не больше одной? Нет! Типичный пример говнокода, который работает только в очень специальных случаях и с грохотом падает при малейшем отклонении от них. 1

@NewOrdered 471 / 234 / 73 Регистрация: 25.05.2012 Сообщений: 1,054 Записей в блоге: 1
	19.07.2013, 11:35 [ТС]	6
	Сообщение от iap Где это ограничение?? Кто помешает мне выполнить INSERT двух записей? Вы проверяете на входе в триггер, что записей не больше одной? Нет! Типичный пример говнокода, который работает только в очень специальных случаях и с грохотом падает при малейшем отклонении от них. Вам - никто. А пользователю, работающему с формой, которая добавляет записи, - я как разработчик этой формы. И если потребуется, то будут реализованы проверки/ограничения на стороне сервера. И почему вы решили, что это будет окончательный вариант решения задачи? Пока что это только дискуссия. Добавлено через 8 минут iap, Возможно, это действительно неправильный подход - создавать код для "специальных случаев". Но я для того и здесь, чтобы понять, где я ошибаюсь. 0

@iap 1116 / 761 / 183 Регистрация: 27.11.2009 Сообщений: 2,269
	19.07.2013, 11:41	7
	Сообщение от NewOrdered iap, Возможно, это действительно неправильный подход - создавать код для "специальных случаев". Но я для того и здесь, чтобы понять, где я ошибаюсь. Дык, я и показываю, где. Может, не всё показываю, но я же не один на форуме? В общем, форма какая-то тут совершенно не при чём. Клиент-серверная технология - это в общем случае один сервер и сколько угодно клиентов, которые должны посылать серверу корректные запросы, а он их должен корректно обработать. Ваш клиент вставляет по одной строке, а другой вставляет сразу миллион! И это нормальная ситуация. 0

@cygapb-007 1313 / 945 / 144 Регистрация: 17.01.2013 Сообщений: 2,348
	19.07.2013, 16:15	10
	invm, а почему LEFT JOIN а не INNER JOIN ? Это же scan вместо seek ? А TourID во view присутствуют все, и однократно... 0

@invm 3499 / 2083 / 742 Регистрация: 02.06.2013 Сообщений: 5,078
	19.07.2013, 16:52	11
	Сообщение от cygapb-007 Это же scan вместо seek Почему вы так решили? Сообщение от cygapb-007 А TourID во view присутствуют все В том-то и дело, что не все, а только те для которых есть записи в [Tourists-Tours]. 0

@invm 3499 / 2083 / 742 Регистрация: 02.06.2013 Сообщений: 5,078
	20.07.2013, 16:01	13
	Сообщение от cygapb-007 в план заглянул Ну план разный может быть. При lef join будет merge и 2 scan'а. А вот при inner может быть, в зависимости от кардинальности, либо скан по vTourCapacity и seek по Tours + Nested Loops, либо merge и 2 scan'а. Но ТС'у-то нужен left... 0