Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Триггер after update  [new]
Gring0
Member

Откуда:
Сообщений: 7
Исходные данные:
CREATE TABLE Заказ
( 
	Номер_заказа         integer  NOT NULL ,
	Дата_оформления      date  NOT NULL ,
	Дата_выполнения      date  NULL ,
	ЗаказID              integer IDENTITY ( 1,1 ) ,
	Сумма_заказа         decimal  NULL ,
	Номер_авто           varchar(20)  NOT NULL ,
	ВладелецID           integer  NULL ,
	Техпаспорт           varchar(40)  NULL 
)
go
ALTER TABLE Заказ
	ADD CONSTRAINT XPKЗаказ PRIMARY KEY (ЗаказID ASC)
go
CREATE TABLE Стоимость_работы
( 
	ЗаказID						integer  NOT NULL ,
	РаботаID					integer  NOT NULL ,
	Стоимость_запчастей			decimal  NULL ,
	Стоимость_выполн_работы		decimal  NULL ,
	Дата_завершения_работы		date  NULL ,
	РаботникиID					integer  NULL 
)
go
ALTER TABLE Стоимость_работы
	ADD CONSTRAINT XPKСтоимость_работы PRIMARY KEY (ЗаказID ASC,РаботаID ASC)
go
ALTER TABLE Заказ
	ADD  FOREIGN KEY (ВладелецID) REFERENCES Владельцы(ВладелецID)
		ON DELETE NO ACTION
		ON UPDATE NO ACTION
go	
ALTER TABLE Заказ
	ADD  FOREIGN KEY (Техпаспорт) REFERENCES Авто(Техпаспорт)
		ON DELETE NO ACTION
		ON UPDATE NO ACTION
go
ALTER TABLE Стоимость_работы
	ADD  FOREIGN KEY (ЗаказID) REFERENCES Заказ(ЗаказID)
		ON DELETE NO ACTION
		ON UPDATE NO ACTION
go
ALTER TABLE Стоимость_работы
	ADD  FOREIGN KEY (РаботаID) REFERENCES Работа(РаботаID)
		ON DELETE NO ACTION
		ON UPDATE NO ACTION
go
ALTER TABLE Стоимость_работы
	ADD  FOREIGN KEY (РаботникиID) REFERENCES Работники(РаботникиID)
		ON DELETE NO ACTION
		ON UPDATE NO ACTION
go


СКРИПТ:
CREATE  TRIGGER СуммаЗаказа
on Стоимость_работы after update
AS
declare @Заказ int
select @Заказ = ЗаказID from inserted
update Заказ
set Сумма_заказа =( select sum(Стоимость_выполн_работы)
from Inserted
Group by (ЗаказID))
where ЗаказID = @Заказ


Суть запроса:
В таблицу ЗАКАЗЫ нужно занести стоимость заказа, который может состоять как из одной, так и из нескольких работ (стоимость работы определена в ХП отдельно).
У меня же получается, что в колонку Сумма_заказа записывает значение, которое было в последней работе заказа. Например:
Заказ 2  	Работа 2		Стоимость_выполн_работы 1560000
Заказ 2  	Работа 12		Стоимость_выполн_работы 320000

В поле Заказ.Сумма_заказа записывается значение 320000

Подскажите пожалуйста! В чем моя ошибка?
22 май 12, 19:20    [12596896]     Ответить | Цитировать Сообщить модератору
 Re: Триггер after update  [new]
RubinDm
Member

Откуда:
Сообщений: 461
Gring0,

create  trigger СуммаЗаказа
on Стоимость_работы after insert, update, delete
as
begin

  if (not(exists(select * from [inserted]))) -- ничего не вставляли
    if (not(exists(select * from [deleted]))) -- ничего не удаляли
      return; -- реально не было никаких изменений, оставим заказы в покое.

  update З set Сумма_заказа =
    ( select sum(СР.Стоимость_выполн_работы)
      from Стоимость_работы СР
      where СР.ЗаказID = З.ЗаказID
    ) 
  from Заказ З
  where З.ЗаказID in
    ( -- Высичляем ID задетых заказов, сумму которых надо пересчитать
      select ЗаказID from inserted [new] -- что-то добавили в заказ
      union all
      select ЗаказID from deleted  [old] -- что-то убрали из заказа
    )

end
go
22 май 12, 20:04    [12597034]     Ответить | Цитировать Сообщить модератору
 Re: Триггер after update  [new]
invm
Member

Откуда: Москва
Сообщений: 9836
RubinDm,

Ваш триггер безобразен. Зачем каждый раз полностью пересчитывать сумму?

Gring0,

Вот примерно так:
create trigger СуммаЗаказа
on Стоимость_работы
after insert, update, delete
as
begin
 if not exists(select * from inserted) and not exists(select * from deleted)
  return;
 
 set nocount on;

 with a as
 (
  select ЗаказID, Стоимость_выполн_работы from inserted
  union all
  select ЗаказID, -Стоимость_выполн_работы from deleted
 ),
 b as
 (
  select
   ЗаказID, sum(Стоимость_выполн_работы) as Стоимость_выполн_работы_Delta
  from
   a
  group by
   ЗаказID
  having
   sum(Стоимость_выполн_работы) > 0
 )
 update З
  set
   Сумма_заказа = Сумма_заказа + b.Стоимость_выполн_работы_Delta
 from
  b join
  Заказ З on З.ЗаказID = b.ЗаказID;
  
end;
go
Можно вообще обойтись без триггеров и работать через представления.
22 май 12, 20:50    [12597171]     Ответить | Цитировать Сообщить модератору
 Re: Триггер after update  [new]
Gring0
Member

Откуда:
Сообщений: 7
Большое спасибо за ваши ответы.

RubinDm,
Ваш пример триггера посчитал сумму как надо, вот только почему-то в сообщениях было следующее:
Warning: Null value is eliminated by an aggregate or other SET operation.
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
Warning: Null value is eliminated by an aggregate or other SET operation.
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
Warning: Null value is eliminated by an aggregate or other SET operation.
.....

Отдельное Вам спасибо за подробные комментарии к примеру!

invm,
Ваш пример триггера оставил значение NULL в поле Сумма_заказа, при этом в сообщениях было следующее:
(1 row(s) affected)
Warning: Null value is eliminated by an aggregate or other SET operation.
(1 row(s) affected)
Warning: Null value is eliminated by an aggregate or other SET operation.
(1 row(s) affected)
Warning: Null value is eliminated by an aggregate or other SET operation.
(1 row(s) affected)
Warning: Null value is eliminated by an aggregate or other SET operation.
.....



Может эти Warning как-то связаны с тем, что триггер срабатывает при вызове ХП ?
22 май 12, 21:44    [12597349]     Ответить | Цитировать Сообщить модератору
 Re: Триггер after update  [new]
RubinDm
Member

Откуда:
Сообщений: 461
invm
RubinDm, Ваш триггер безобразен.
Да, Вы правы - чувство прекрасного у всех развито как попало. В моем понимании "безобразно" - это применение CTE (или любого иного инструментария) там, где в этом нет реальной необходимости. Предлагаю изучить аналогичный вариант запроса без CTE, в котором из 23 строк говнокода 12 убыло без ущерба для дела. Но нет худа без добра - теперь мы знаем, что Вы умеете много всякого и по-разному, хоть и не всегда прямо.
update З set З.Сумма_заказа = З.Сумма_заказа + b.Delta
from Заказы З
join
( select ЗаказID, Delta = sum(Стоимость_выполн_работы)
  from
  ( select ЗаказID, +Стоимость_выполн_работы from inserted
    union all
    select ЗаказID, -Стоимость_выполн_работы from deleted
  ) a
  group by ЗаказID
) b on b.ЗаказID = З.ЗаказID



invm
Зачем каждый раз полностью пересчитывать сумму?

Видимо, Вам неизвестно, что значение поля [Стоимость_выполн_работы] таблицы [Стоимость_работы] иногда может разительно отличаться от того значения, которое указано в таблице [INSERTED] в триггере.
22 май 12, 21:51    [12597379]     Ответить | Цитировать Сообщить модератору
 Re: Триггер after update  [new]
invm
Member

Откуда: Москва
Сообщений: 9836
RubinDm
Видимо, Вам неизвестно, что значение поля [Стоимость_выполн_работы] таблицы [Стоимость_работы] иногда может разительно отличаться от того значения, которое указано в таблице [INSERTED] в триггере.
Репро в студию.
22 май 12, 21:58    [12597390]     Ответить | Цитировать Сообщить модератору
 Re: Триггер after update  [new]
RubinDm
Member

Откуда:
Сообщений: 461
Gring0
RubinDm, Ваш пример триггера посчитал сумму как надо
Не сомневаюсь :)
Gring0
вот только почему-то в сообщениях было следующее:
Warning: Null value is eliminated by an aggregate or other SET operation.
Это означает, что в процессе расчета sum(СР.Стоимость_выполн_работы) серверу попадались не указанные значения в поле [Стоимость_выполн_работы]. В таком случае сервер просто игнорирует запись и она не влияет на итоговый результат расчета суммы. Но сам факт, что такие записи были (с пустыми значениями), интерпретируется сервером как "повод задуматься". Будь там ноль, а не нул - сервер слова бы не сказал.


Про (1 row(s) affected) - и так все понятно. Или нет?
22 май 12, 22:00    [12597395]     Ответить | Цитировать Сообщить модератору
 Re: Триггер after update  [new]
invm
Member

Откуда: Москва
Сообщений: 9836
RubinDm
Видимо, Вам неизвестно, что значение поля [Стоимость_выполн_работы] таблицы [Стоимость_работы] иногда может разительно отличаться от того значения, которое указано в таблице [INSERTED] в триггере.

Есть конечно извращенный сценарий, когда это возможно. Но таковой является всего лишь ошибкой реализации триггеров.
Так что интересно увидеть ваше репро.
22 май 12, 22:24    [12597492]     Ответить | Цитировать Сообщить модератору
 Re: Триггер after update  [new]
RubinDm
Member

Откуда:
Сообщений: 461
invm
RubinDm
Видимо, Вам неизвестно, что значение поля [Стоимость_выполн_работы] таблицы [Стоимость_работы] иногда может разительно отличаться от того значения, которое указано в таблице [INSERTED] в триггере.
Репро в студию.
вот, сравните два разных результата:
use master
go

drop database dbDemo
go

create database dbDemo
go

use dbDemo
go

create table VVV
  ( ID int identity not null
  , V int not null
  )
go

create trigger VVV_ADD1
  on VVV after insert
as
begin

  update OI set OI.V = OI.V + 1
  from VVV OI
  where OI.ID in (select [new].ID from inserted [new])
  
end
go

create trigger VVV_ADD2
  on VVV after insert
as
begin

  update OI set OI.V = OI.V + 2
  from VVV OI
  where OI.ID in (select [new].ID from inserted [new])
  
end
go

insert into VVV ( V ) values ( 100 )
select * from VVV
go

use master
go

drop database dbDemo
go

create database dbDemo
go

use dbDemo
go

create table VVV
  ( ID int identity not null
  , V int not null
  )
go

create trigger VVV_ADD1
  on VVV after insert
as
begin

  update OI set OI.V = [new].V + 1
  from VVV OI
  join inserted [new] on [new].ID = OI.ID
  where OI.ID in (select [new].ID from inserted [new])
  
end
go

create trigger VVV_ADD2
  on VVV after insert
as
begin

  update OI set OI.V = [new].V + 2
  from VVV OI
  join inserted [new] on [new].ID = OI.ID
  where OI.ID in (select [new].ID from inserted [new])
  
end
go

insert into VVV ( V ) values ( 100 )
select * from VVV
go
22 май 12, 22:25    [12597498]     Ответить | Цитировать Сообщить модератору
 Re: Триггер after update  [new]
RubinDm
Member

Откуда:
Сообщений: 461
invm
Есть конечно извращенный сценарий, когда это возможно. Но таковой является всего лишь ошибкой реализации триггеров. Так что интересно увидеть ваше репро.
Сколь бы извращенным не был сценарий, сиквел формально прав, отражая в [INSERTED] именно те значения, которые INSERTED, а не те, которые AFFECTED. И слава Богу, что все работает именно так, как работает, пусть все так и остается.
22 май 12, 22:30    [12597520]     Ответить | Цитировать Сообщить модератору
 Re: Триггер after update  [new]
Gring0
Member

Откуда:
Сообщений: 7
RubinDm
Про (1 row(s) affected) - и так все понятно. Или нет?

С этим все понятно.
Насчет вопроса: спасибо, разобрался. Если бы так обьяснял мне мой преподователь было бы здорово...
22 май 12, 22:38    [12597560]     Ответить | Цитировать Сообщить модератору
 Re: Триггер after update  [new]
invm
Member

Откуда: Москва
Сообщений: 9836
RubinDm,

Примерно такой ответ и ожидался. Банальнейшие ошибки проектирования триггеров.
22 май 12, 22:58    [12597627]     Ответить | Цитировать Сообщить модератору
 Re: Триггер after update  [new]
RubinDm
Member

Откуда:
Сообщений: 461
Gring0
спасибо, разобрался. Если бы так обьяснял мне мой преподователь было бы здорово...

уф.. препоДАватель. От слова ДАвать, а не ДОставлять. Не за что ;)
23 май 12, 00:44    [12597957]     Ответить | Цитировать Сообщить модератору
 Re: Триггер after update  [new]
Объясните популярно
Guest
RubinDm
Gring0,

create  trigger СуммаЗаказа
on Стоимость_работы after insert, update, delete
as
begin

  if (not(exists(select * from [inserted]))) -- ничего не вставляли
    if (not(exists(select * from [deleted]))) -- ничего не удаляли
      return; -- реально не было никаких изменений, оставим заказы в покое.

 


Здравствуйте

Объястните плз. зачем, когда у нас тригер after insert, update, delete


вы делаете:

  if (not(exists(select * from [inserted]))) -- ничего не вставляли
    if (not(exists(select * from [deleted]))) -- ничего не удаляли
      return; -- реально не было никаких изменений, оставим заказы в покое.


могут быть варианты?????
23 май 12, 13:02    [12600444]     Ответить | Цитировать Сообщить модератору
 Re: Триггер after update  [new]
Glory
Member

Откуда:
Сообщений: 104751
Объясните популярно
могут быть варианты?????

Могут - когда запросом не затронуто ни одной записи
23 май 12, 13:05    [12600476]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить