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

Откуда:
Сообщений: 16
Добрый день!
Помогите реализовать обновление вычисляемого поля через триггер.

Исходные данные:
Есть две таблицы: product - в ней содержатся данные о товаре и category - данные о категориях товаров

В таблице product есть два поля: cost_price - себестоимость и retail_price - цена продажи, которая рассчитывается как произведение: cost_price * category_mark_up (наценка из таблицы category).

Код триггера на вставку отрабатывает нормально, retail_price считается верно. Вот сам код:
CREATE TRIGGER i_trigger_retail_price
    ON product
    AFTER INSERT
AS
BEGIN
    DECLARE @rt INT
    DECLARE @cp INT
    DECLARE @cmu FLOAT
 
SELECT @cp = cost_price
FROM product
WHERE id_product = (SELECT id_product FROM inserted)
 
SELECT @cmu = category_mark_up
FROM category 
WHERE id_category = (SELECT id_category FROM inserted)
 
SET @rt = @cp*@cmu
 
UPDATE product SET retail_price = @rt WHERE id_product = (SELECT id_product FROM inserted)
 
END



Я поначалу пытался обновление вычисляемого поля реализовать через этот же триггер, то есть AFTER INSERT, UPDATE. Но в этом случае обновление работало только для связки cost_price - retail_price. Обновление поля category_mark_up никак не влияло на значение вычисляемого поля retail_price.
То есть меняю значение cost_price - меняется значение retail_price. Меняю значение category_mark_up - значение retail_price не меняется ни в какую.



Свою ошибку я понял. Я в триггере, который навесил на таблицу product, пытаюсь обновить поле catrgory_mark_up из другой таблицы (таблицы category), что невозможно.

Надо сначала создать второй триггер для обновления поля category_mark_up.

Но как быть потом? Как увязать вместе два триггера? Ведь еще надо ввести проверку на то, изменились ли исходные поля.


Буду благодарен, если поможете с кодом ))
17 сен 16, 13:43    [19676744]     Ответить | Цитировать Сообщить модератору
 Re: Триггер на обновление вычисляемого поля  [new]
don_zaresh
Member

Откуда:
Сообщений: 16
Содержание таблиц:

category
---------
id_category (PK)
category_name
category_mark_up



product
--------
id_product (PK)
id_category (FK)
cost_price
retail_price
17 сен 16, 13:55    [19676770]     Ответить | Цитировать Сообщить модератору
 Re: Триггер на обновление вычисляемого поля  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
don_zaresh,

сделайте представление и всё.
и да в inserted может быть мнооооого записей, а вы считаете как одну
17 сен 16, 14:06    [19676795]     Ответить | Цитировать Сообщить модератору
 Re: Триггер на обновление вычисляемого поля  [new]
don_zaresh
Member

Откуда:
Сообщений: 16
TaPaK, добавление по одной записи делал намеренно. На форме в самой программе возможно только так: новая строка, вбиваем данные в edit'ы и жмем кнопку "Добавить"

Пытаюсь создать представление, выдает ошибку "Неоднозначное имя столбца "id_category".

CREATE VIEW p_prod
AS
SELECT id_category, category_mark_up, id_product, cost_price, retail_price
FROM product p, category c
WHERE p.id_category = c.id_category


Вот код самого триггера. Хоть что-то в нем правильно? ))

Это на вставку. На обновление пока не додумал

CREATE TRIGGER i_trigger_retail
ON p_prod
INSTEAD OF INSERT
AS
BEGIN

INSERT INTO category (category_mark_up)
SELECT category_mark_up
FROM inserted i

INSERT INTO product (cost_price, id_category)
SELECT cost_price, id_category
FROM inserted i, category c
WHERE i.id_category = c.id_category

UPDATE product SET retail_price = cost_price * category_mark_up
WHERE i.id_category = c.id_category
END
17 сен 16, 14:44    [19676857]     Ответить | Цитировать Сообщить модератору
 Re: Триггер на обновление вычисляемого поля  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
don_zaresh,
автор
SELECT id_category, category_mark_up, id_product, cost_price, retail_price
где алиасы?
17 сен 16, 15:17    [19676908]     Ответить | Цитировать Сообщить модератору
 Re: Триггер на обновление вычисляемого поля  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
и вообще смысл такой вьюхи?

автор
CREATE VIEW p_prod
AS
SELECT a.id_category, category_mark_up, id_product, cost_price, retail_price,
category_mark_up*cost_price
FROM product p, category c
WHERE p.id_category = c.id_category
17 сен 16, 15:20    [19676917]     Ответить | Цитировать Сообщить модератору
 Re: Триггер на обновление вычисляемого поля  [new]
don_zaresh
Member

Откуда:
Сообщений: 16
TaPaK, исправил

CREATE VIEW p_prod
AS
SELECT c.id_category as id_category, c.category_mark_up as category_mark_up, p.id_product as id_product,
p.cost_price as cost_price, p.retail_price as retail_price
FROM product p, category c
WHERE p.id_category = c.id_category


А насчет представления все-таки непонятно, как оно может помочь. Допустим, что в самом представлении я изменил значение retail_price по формуле.

Предположим, мне надо изменить наценку на категорию товара. Тогда я меняю значение напрямую в таблице category (в программе это реализуется через пункт меню "Справочники / Категории товаров")

И снова сталкиваюсь с ситуацией, когда надо было писать триггер на обновление на две таблицы.


Или я что-то не понял?
17 сен 16, 15:32    [19676943]     Ответить | Цитировать Сообщить модератору
 Re: Триггер на обновление вычисляемого поля  [new]
iljy
Member

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

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

ну и
На форме в самой программе возможно только так: новая строка, вбиваем данные в edit'ы и жмем кнопку "Добавить"


отмазка из серии " я не делаю бакапы потому, что я не буду ломать компьютер". В один не очень прекрасный день (особенно если пихать триггеры куда ни попадя) произойдет обновление нескольких строк, и посыплются странные и загадочные ошибки. При всем при том правильный запрос гораздо компактнее и не требует подобных извращений.
17 сен 16, 23:09    [19678055]     Ответить | Цитировать Сообщить модератору
 Re: Триггер на обновление вычисляемого поля  [new]
don_zaresh
Member

Откуда:
Сообщений: 16
iljy, понятно. Буду делать через представление.


Просто мне уже самому интересно, как все-таки такая задача реализуется через триггеры. На будущее, так сказать.

Первый триггер - проверяет обновление поля cost_price. Вешается на таблицу product
Второй триггер - проверяет обновление category_mark_up. Вешается на таблицу category.

А вот дальше, как я понимаю, в таблице product надо проверить условие, обновились ли эти два поля. И если обновилось category_mark_up, то взять его значение.

Вот именно пример кода проверки условия я не могу найти. Может, у вас есть подобные примеры?
18 сен 16, 14:19    [19679250]     Ответить | Цитировать Сообщить модератору
 Re: Триггер на обновление вычисляемого поля  [new]
iljy
Member

Откуда:
Сообщений: 8711
don_zaresh
iljy, понятно. Буду делать через представление.


Просто мне уже самому интересно, как все-таки такая задача реализуется через триггеры. На будущее, так сказать.

Первый триггер - проверяет обновление поля cost_price. Вешается на таблицу product
Второй триггер - проверяет обновление category_mark_up. Вешается на таблицу category.

А вот дальше, как я понимаю, в таблице product надо проверить условие, обновились ли эти два поля. И если обновилось category_mark_up, то взять его значение.

Вот именно пример кода проверки условия я не могу найти. Может, у вас есть подобные примеры?


Примерно так:
CREATE TRIGGER i_trigger_retail_price
    ON product
    AFTER INSERT, UPDATE
AS
BEGIN

   UPDATE p SET p.retail_price = p.cost_price * c.category_mark_up
   FROM inserted i left join deleted d on d.id_product = i.id_product
            JOIN product p on p.id_product = i.id_product
            JOIN category c on c.id_category = p.id_category
   WHERE d.id_product is null or i.cost_price != d.cost_price

END


На вторую таблицу сами пишите.
18 сен 16, 16:10    [19679444]     Ответить | Цитировать Сообщить модератору
 Re: Триггер на обновление вычисляемого поля  [new]
don_zaresh
Member

Откуда:
Сообщений: 16
iljy, спасибо большое! сейчас опробую этот код
18 сен 16, 19:50    [19679909]     Ответить | Цитировать Сообщить модератору
 Re: Триггер на обновление вычисляемого поля  [new]
don_zaresh
Member

Откуда:
Сообщений: 16
Товарищи, не могли бы еще подсказать? ))
Продолжаю разбираться с триггерами. Вот этот работает сравнивает количество продаваемого товара и его остаток на складе. Если остаток меньше, то делает откат. Если все нормально, то уменьшает количество на складе.

При попытке создать этот триггер Microsoft SQL Server выдает ошибки:

1) Неправильный синтаксис около ключевого слова BEGIN (перед строчкой ROLLBACK TRAN)
2) Неправильный синтаксис около ключевого слова ELSE

В чем может быть дело?

CREATE TRIGGER i_trigger_ogren_quantity
	ON selling 
	FOR INSERT
AS
DECLARE @x INT, @y INT, @z INT
IF @@ROWCOUNT=1
BEGIN
     IF NOT EXISTS(SELECT * FROM inserted
          WHERE inserted.sell_quantity<= (SELECT production.production_quantity FROM production, selling
	  WHERE production.id_consigment = selling.id_consigment AND production.id_product = selling.id_product)
              BEGIN
                    ROLLBACK TRAN
                    PRINT 
                    'Количество вводимого товара превышает количество в партии. Операция отменена'
              END

      ELSE
	      BEGIN
                    SELECT @y=i.id_product, @x=i.sell_quantity, @z = i.id_consigment
                    FROM selling A, inserted i, production B
                    WHERE A.id_product=i.id_product AND A.id_consigment=B.id_consigment
                    UPDATE production
                    SET production_quantity=production_quantity-@x
                    WHERE id_product=@y AND id_consigment=@z	
              END
END
18 сен 16, 22:30    [19680626]     Ответить | Цитировать Сообщить модератору
 Re: Триггер на обновление вычисляемого поля  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31442
don_zaresh
В чем может быть дело?
Закрывающую скобку пропустили.
18 сен 16, 22:55    [19680718]     Ответить | Цитировать Сообщить модератору
 Re: Триггер на обновление вычисляемого поля  [new]
don_zaresh
Member

Откуда:
Сообщений: 16
alexeyvg, спасибо! Я даже на это не обратил внимание.

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

Создать данный триггер невозможно. Выдает:

Сообщение 2113, уровень 16, состояние 1, процедура u_trigger_sell_quantity, строка 57 (это самая последняя строка)
Невозможно создать INSTEAD OF DELETE или INSTEAD OF UPDATE TRIGGER "u_trigger_sell_quantity" для таблицы "selling", так как таблица содержит FOREIGN KEY с каскадными инструкциями DELETE или UPDATE.

Да, действительно, один из внешних ключей, которые образуют составной первичный ключ, должен обновляться каскадно (это код продукта).
Ошибка из-за чего может быть? Ругается на возможный цикл? Как можно обойти проблему?


CREATE TRIGGER u_trigger_sell_quantity
	ON selling
	INSTEAD OF UPDATE
AS
DECLARE @prod INT, @prod_old INT /*Код товара*/
DECLARE @sq INT, @sq_old INT /*Кол-во в накладной*/
DECLARE @inv INT, @inv_old INT /*Код накладной*/
DECLARE @pq INT /*Кол-во на складе*/
DECLARE @cs INT, @cs_old INT /*Код партии*/

DECLARE CUR1 CURSOR FOR
SELECT id_invoice, id_product, id_consigment, sell_quantity
FROM inserted

DECLARE CUR2 CURSOR FOR
SELECT id_invoice, id_product, id_consigment, sell_quantity
FROM deleted

OPEN CUR1
OPEN CUR2

FETCH NEXT FROM CUR1 INTO @inv, @prod, @cs, @sq
FETCH NEXT FROM CUR2 INTO @inv_old, @prod_old, @cs_old, @sq_old

WHILE @@FETCH_STATUS=0
BEGIN
	SELECT @pq = production_quantity
	FROM production
	WHERE id_product = @prod AND id_consigment = @cs

	IF @pq >= @sq
	     BEGIN
	     RAISERROR ('Количество изменено', 16, 10)
	     UPDATE selling SET sell_quantity = @sq, id_product = @prod, id_consigment = @cs
	     WHERE id_invoice = @inv

	     UPDATE production
	     SET production_quantity = production_quantity +@sq_old
	     WHERE id_product = @prod_old AND id_consigment = @cs_old
             END


	     IF EXISTS (SELECT * FROM production WHERE id_product = @prod AND id_consigment = @cs)
	     UPDATE production		
	     SET production_quantity = production_quantity - @sq
	     WHERE id_product = @prod AND id_consigment = @cs

	     ELSE RAISERROR ('Запись не изменена', 16, 10)

FETCH NEXT FROM CUR1 INTO @inv, @prod, @cs, @sq
FETCH NEXT FROM CUR2 INTO @inv_old, @prod_old, @cs_old, @sq_old

END

CLOSE CUR1
CLOSE CUR2
DEALLOCATE CUR1
DEALLOCATE CUR2


К сообщению приложен файл. Размер - 51Kb
19 сен 16, 15:05    [19683117]     Ответить | Цитировать Сообщить модератору
 Re: Триггер на обновление вычисляемого поля  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
don_zaresh
Ошибка из-за чего может быть? Ругается на возможный цикл? Как можно обойти проблему?

реализовать бизнесс логику на хранимой процедуре и не мучать сервер триггером такого типа
19 сен 16, 15:18    [19683204]     Ответить | Цитировать Сообщить модератору
 Re: Триггер на обновление вычисляемого поля  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
don_zaresh,
автор
Да, действительно, один из внешних ключей, которые образуют составной первичный ключ, должен обновляться каскадно (это код продукта).

вы храните код продукта во многих таблицах? тогда вам дорога к изучению нормальных форм
19 сен 16, 15:21    [19683216]     Ответить | Цитировать Сообщить модератору
 Re: Триггер на обновление вычисляемого поля  [new]
iap
Member

Откуда: Москва
Сообщений: 47001
Для данной задачи подходит индекированное представление.
И не надо в явном виде хранить лишний результат расчёта.
Он будет храниться и пересчитываться автоматически.
Правда, SELECTы выполнять из этого представления, а не из таблиц.
19 сен 16, 15:22    [19683228]     Ответить | Цитировать Сообщить модератору
 Re: Триггер на обновление вычисляемого поля  [new]
don_zaresh
Member

Откуда:
Сообщений: 16
TaPaK, код продукта хранится только в одной таблице. В остальных он работает как внешний ключ. Задача поставлена так, что надо учитывать срок годности товара. Поэтому пришлось организовать таблицу production, в которой хранится информация о каждой партии. И продажа товара производится путем выбора его из партии, которая еще не истекла.

Поэтому код продукта появился и в таблице selling как часть составного ключа. То есть каждая проданная позиция имеет первичный составной ключ в виде "код накладной-код партии-код продукта"

Может, все сделано вкривь и вкось, не спорю. Первый опыт работы с базами ))
19 сен 16, 15:34    [19683312]     Ответить | Цитировать Сообщить модератору
 Re: Триггер на обновление вычисляемого поля  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
don_zaresh,

работайте с представлениями и не стройте какую-то безумную логику с каскадными обновлениями на триггерах, в которых вы потом ещё и разбираться будете что из-за кого-чего. Или переместите логику в процедурs и модификацию делайте через них
19 сен 16, 15:39    [19683348]     Ответить | Цитировать Сообщить модератору
 Re: Триггер на обновление вычисляемого поля  [new]
don_zaresh
Member

Откуда:
Сообщений: 16
iap, хорошо, спасибо, сейчас посмотрю, что это такое.

А вообще, все-таки стоит пытаться организовать это решение на сервере или проще сделать через код самой программы? Буду делать в Делфи
19 сен 16, 15:41    [19683359]     Ответить | Цитировать Сообщить модератору
 Re: Триггер на обновление вычисляемого поля  [new]
don_zaresh
Member

Откуда:
Сообщений: 16
iap, про индексированное представление почитал. Два вопроса:

1) как в нем организовать расчет остатков при вставке, удалении, обновлении? Оно же по сути работает как обычное представление. Вывести остатки по одной какой-то формуле не проблема. А тут три разные ситуации

2) Надо ли тогда вообще создавать таблицу selling?
19 сен 16, 16:17    [19683640]     Ответить | Цитировать Сообщить модератору
 Re: Триггер на обновление вычисляемого поля  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
don_zaresh
iap, про индексированное представление почитал. Два вопроса:

1) как в нем организовать расчет остатков при вставке, удалении, обновлении? Оно же по сути работает как обычное представление. Вывести остатки по одной какой-то формуле не проблема. А тут три разные ситуации

ситуация... говорят перестань пользоваться молотком - есть отвёртка, на выходе взяли отвёртку и спрашивают как ей забить гвоздь....
19 сен 16, 16:21    [19683669]     Ответить | Цитировать Сообщить модератору
 Re: Триггер на обновление вычисляемого поля  [new]
don_zaresh
Member

Откуда:
Сообщений: 16
TaPaK, если можете подсказать что-то конкретное, подскажите.

Обычное представление выводит то, что хранится в базовых таблицах. Ну и можно вычисляемые поля добавить.
Через представление обновлять содержимое базовых таблиц, как я помню, невозможно.

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

Мне то надо следить за остатками по каждой записи:
оформляется накладная №1
в нее добавляем товар 1 из партии 2, товар 3 из партии 10 и т.д., то есть список товаров.

Если остаток в этой записи меняется, то его надо изменить в той партии, откуда он списывался.

То есть мне не надо считать общий остаток по все партиям.

Если у кого-то есть ссылочка на пример, поделитесь, пожалуйста.

Пока буду рассматривать второй вариант - хранимые процедуры ))
19 сен 16, 16:46    [19683849]     Ответить | Цитировать Сообщить модератору
 Re: Триггер на обновление вычисляемого поля  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
don_zaresh,

автор
Через представление обновлять содержимое базовых таблиц, как я помню, невозможно.

вы удивитесь
19 сен 16, 17:36    [19684181]     Ответить | Цитировать Сообщить модератору
 Re: Триггер на обновление вычисляемого поля  [new]
don_zaresh
Member

Откуда:
Сообщений: 16
TaPaK, ну можно так можно. Не суть важно. Будем добивать триггеры, а дергаться от способа к способу
19 сен 16, 19:50    [19684696]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить