VPF::Как добавить в ХП INSERT команду на Update

thomas

Дата 22.2.2008, 12:39 (ссылка)

(нет голосов)

Загрузка ...

Доцент... почти

Профиль
Группа: Завсегдатай
Сообщений: 1385
Регистрация: 3.10.2006
Где: " Сказочное королевство"

Репутация: нет
Всего: 65

Приветствую всех.
Уважаемые повелители SQL, просветите несмышленого студента.
Имею хранимую процедуру для вставки записи в таблицу содержащую данные об израсходованных материалах.

Код


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_WerkbonDetail_AddRow]
    @werkbonId INT,
    @artikelId INT,
    @aantal INT,
    @korting INT,
    @btw INT,
    @afgerekend BIT,
    @regelId INT OUTPUT
AS
BEGIN
    DECLARE @verkoopPrijs MONEY
    SET @verkoopPrijs =(SELECT tblArtikel.HuidigePrijs * (tblArtikel.Winst * 0.01 + 1) AS Prijs
                        FROM tblArtikel WHERE tblArtikel.ArtikelId = @artikelId)
    INSERT INTO tblWerkbonDetail
        (
        WerkbonId,
        ArtikelId,
        Aantal,
        VerkoopPrijs,
        Korting,
        Btw,
        Afgerekend
        )
    VALUES
        (
        @werkbonId,
        @artikelId,
        @aantal,
        @verkoopPrijs,
        @korting,
        @btw,
        @afgerekend
        )
    SELECT @regelId = SCOPE_IDENTITY()
END

Так вот здесь есть поле aantal - количество, израсходованного материала. И мне бы очень хотелось добавить в данную хранимую процедуру инструкцию, которая бы выполнила Update в таблице содержащей данные о материале на складе. В частности, из поля в котором хранится данные о количестве товара на складе, вычесть значение переменной @aantal и сохранить получившийся остаток.

Вот так выглядит моя хранимая процедура для update всех данных о материале на складе.

Код


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[usp_Artikel_Update]
    @artikelId INT,
    @subCategorieId INT,
    @artikelNaam NVARCHAR(50), 
    @omschrijving NVARCHAR(150),
    @eenheid NCHAR(4),
    @minVoorraad INT,
    @voorraad INT,
    @winst INT,
    @huidigePrijs MONEY
AS
BEGIN
    DECLARE @modifiedDate DATETIME;
    SET @modifiedDate = GETDATE();
    UPDATE tblArtikel
    SET
        SubCategorieId = @subCategorieId,
        ArtikelNaam = @artikelNaam, 
        Omschrijving = @omschrijving,
        Eenheid = @eenheid,
        MinVoorraad = @minVoorraad,
        Voorraad = @voorraad,
        Winst = @winst,
        HuidigePrijs = @huidigePrijs,
        ModifiedDate = @modifiedDate
    WHERE
        ArtikelId = @artikelId
END

И тут меня интересует поле Voorraad - запас. Его надо уменьшить на величину переменной @aantal из первой ХП и сохранить.

Надеюсь никого не запутал и понятно обьяснил что мне надо.

Навсякий случай повторюсь.
Хочеться в одной ХП выполнить два действа:
- вставить строку в одну таблицу;
- уменьшить значение поля в другой таблице на величину, записываемую в первую таблицу, и сохранить получившееся значение во второй таблице.

Заранее спасибо.

ЗЫ понимаю что это должно быть не очень сложно, для тех кто знает. Но я пока еще учусь и еще этого не знаю.

--------------------

Крепко жму горло, искренне ваш Thomas. (С)vingrad
Некоторые сорта флоры буквально за одно мгновение превращают нас в фауну!
Проблемы негров шерифа не волнуют.

Itsys

Дата 22.2.2008, 13:39 (ссылка)

(нет голосов)

Загрузка ...

Эксперт

Профиль
Группа: Завсегдатай
Сообщений: 1338
Регистрация: 21.1.2008
Где: г. Москва

Репутация: 1
Всего: 34

SET Voorraad = Voorraad - @aantal

under_sun

Дата 23.2.2008, 14:24 (ссылка)

(нет голосов)

Загрузка ...

Шустрый

Профиль
Группа: Участник
Сообщений: 141
Регистрация: 3.10.2007
Где: Ебург

Репутация: 5
Всего: 17

Можно через триггер, который обновляет поле Voorraad в таблице tblArtikel при добывлении в таблицу tblWerkbonDetail:

Код


CREATE TRIGGER UpdateInArtikel
ON tblWerkbonDetail 
AFTER INSERT
AS
    UPDATE tblArtikel SET Voorraad = Voorraad - i.Aantal
    FROM inserted i
    WHERE tblArtikel.ArtikelId = i.ArtikelId

--------------------

TMTOWTDI

thomas

Дата 23.2.2008, 15:56 (ссылка)

(нет голосов)

Загрузка ...

Доцент... почти

under_sun,
Приветствую.
Возможно с триггером будет более правильно. Но я еще с ними не работал никогда. И совершенно не представляю как их создавать и как и откуда вызывать.
Например про UDF знаю что, ее можно вызвать только из Хранимой процедуры, а не из приложения которое работает с базой.
Потому, прошу пояснить про ваш триггер.
Где его вызывать? Или он сам срабатывает? Как он срабатывает?

Пока вот в конце концов написал такую ХП(в виду не значительного знания предмета, проблемы с синтаксисом)

Код


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_WerkbonDetail_AddRow]
    @werkbonId INT,
    @artikelId INT,
    @aantal INT,
    @korting INT,
    @btw INT,
    @afgerekend BIT,
    @regelId INT OUTPUT
AS
BEGIN
    DECLARE @verkoopPrijs MONEY
    SET @verkoopPrijs =(SELECT tblArtikel.HuidigePrijs * (tblArtikel.Winst * 0.01 + 1) AS Prijs
                        FROM tblArtikel WHERE tblArtikel.ArtikelId = @artikelId)
    INSERT INTO tblWerkbonDetail
        (
        WerkbonId,
        ArtikelId,
        Aantal,
        VerkoopPrijs,
        Korting,
        Btw,
        Afgerekend
        )
    VALUES
        (
        @werkbonId,
        @artikelId,
        @aantal,
        @verkoopPrijs,
        @korting,
        @btw,
        @afgerekend
        )
    SELECT @regelId = SCOPE_IDENTITY()
END

BEGIN
    DECLARE @modifiedDate DATETIME;
    SET @modifiedDate = GETDATE();
    DECLARE @voorraad INT;
    SET @voorraad = (SELECT Voorraad FROM tblArtikel WHERE ArtikelId = @artikelId)
    UPDATE tblArtikel
    SET
        Voorraad = @voorraad - @aantal,
        ModifiedDate = @modifiedDate
    WHERE
        ArtikelId = @artikelId
END

--------------------

under_sun

Дата 23.2.2008, 16:18 (ссылка)

(нет голосов)

Загрузка ...

Шустрый

Профиль
Группа: Участник
Сообщений: 141
Регистрация: 3.10.2007
Где: Ебург

Репутация: 5
Всего: 17

Триггер - это хранимая процедура, которая срабатывает автоматически при удалении, добавлении или изменении данных в таблице.
В триггере автоматически создаются две таблицы, deleted - при удалении, и inserted - при добавлении ( или обе при update ), которые содержат соответсвенно удаленные или добавленные строки.
Так в моем примере те записи которые мы добавляем в таблицу tblWerkbonDetail, содержатся в inserted, и в тиргере автоматически выполняется update таблицы tblArtikel при каждой вставке.

--------------------

TMTOWTDI

thomas

Дата 23.2.2008, 17:28 (ссылка)

(нет голосов)

Загрузка ...

Доцент... почти

under_sun,
Приветствую.
Если я правильно понял, то мне надо:
-  приведенный тобой выше код сохранить в текстовый файл с расширением sql;
- и выполнить его в Management Studio на моем сервере, создав тем самым в моей БД триггер.

И потом при каждом добавлении строк в таблицу  tblWerkbonDetail будет автоматически срабатывать этот триггер, изменяя остаток заданного материала в таблице tblArtikel.
А то что я добавил в ХП нужно убрать.

Добавлено через 56 секунд
ЗЫ подскажи где бы что почитать более подробно о создании и использовании триггеров.

Добавлено через 12 минут и 10 секунд
Похоже мне не судьба воспользоваться триггерами в моей БД.
У меня MS SQL EXPRESS server 2005.
В моей БД в разделе программирование есть раздел триггеры базы данных.
Но в контекстном меню есть только один пункт - "обновить"   smile

Тогда как для ХП или UDF есть пункт "создать".
Сделал скриптик с твоим кодом, добавил туда кого юзать. Попытался его выполнить.
Студия открылась, соеденилась с сервером, проверила синтаксис скрипта, осталась довольна.
Выполнила скрипт, отрапортовала что все прошло удачно.
Но триггер в БД не добавился. smile

Слушай ... обидно ... smile

--------------------

under_sun

Дата 23.2.2008, 19:17 (ссылка)

(нет голосов)

Загрузка ...

Шустрый

Профиль
Группа: Участник
Сообщений: 141
Регистрация: 3.10.2007
Где: Ебург

Репутация: 5
Всего: 17

Цитата(thomas @ 23.2.2008, 17:28

)

подскажи где бы что почитать более подробно о создании и использовании триггеров.

http://msdn2.microsoft.com/ru-ru/library/ms203721.aspx
Возможно, эта документация уже была установлена вместе с сервером.

Цитата(thomas @ 23.2.2008, 17:28

)

В моей БД в разделе программирование есть раздел триггеры базы данных.
Но в контекстном меню есть только один пункт - "обновить"

Никогда раньше этого не замечал, но в Management Studio почему-то триггеры не отображаются smile

. Однако это вовсе не значит, что триггер не добавился к бд. Проверить можно так:

Код


SELECT * FROM sys.triggers

--------------------

TMTOWTDI

thomas

Дата 24.2.2008, 11:19 (ссылка)

(нет голосов)

Загрузка ...

Доцент... почти

under_sun,
Приетствую.

Цитата

Никогда раньше этого не замечал, но в Management Studio почему-то триггеры не отображаются smile. Однако это вовсе не значит, что триггер не добавился к бд. Проверить можно так:

Попробовал еще раз выполнить скрипт по созданию триггера, студия ругнулась, заявила что такой обьект уже есть.
Получаеться триггер все же создан.
BOL я себе отдельно поставил. Начинаю просматривать что там есть и почитывать.
Надо бы еще по Intuit.ru полазить, может там есть лекции и по MS SQL.
А то есть необходимость более подробно изучить возможности ХП, UDF и триггеров. А так же администрирование доступа к серверу и бд соответственно.

Спасибо за помощь.
С наилучшими пожеланиями smile

--------------------

Killerman

Дата 8.3.2008, 23:49 (ссылка)

(нет голосов)

Загрузка ...

Шустрый

Профиль
Группа: Участник
Сообщений: 137
Регистрация: 26.10.2007

Репутация: нет
Всего: нет

Так всетаки, вы уже выяснили как в экспрессе добавлять триггеры? Подскажите если знаете. А то эта тема какая то нездоровая. smile

metis

Дата 9.3.2008, 08:27 (ссылка)

(нет голосов)

Загрузка ...

Шустрый

Профиль
Группа: Участник
Сообщений: 84
Регистрация: 29.1.2008

Репутация: нет
Всего: нет

Триггер вешается на конкретную таблицу, добавить его можно не в разделе программирования, а в разделе tables, кликаешь по + рядом с нужной таблицей, там будет вкладка Triggers.

0 Пользователей читают эту тему (0 Гостей и 0 Скрытых Пользователей)
0 Пользователей:
« Предыдущая тема \| MS SQL Server \| Следующая тема »