Этот триггер не представляет из себя ничего особенного
Код
CREATE TRIGGER [TBL_CURRENCY#CHANGE_RATE] ON dbo.TBL_CURRENCY
FOR INSERT, UPDATE
AS
IF UPDATE(CUR_RATE)
BEGIN
DECLARE @l_iCUR_ID int, @l_fCUR_RATE float, @l_iSG_ID int;
DECLARE cInserted CURSOR LOCAL FOR Select CUR_ID, CUR_RATE from Inserted;
OPEN cInserted;
FETCH NEXT FROM cInserted INTO @l_iCUR_ID, @l_fCUR_RATE;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
DECLARE cSG CURSOR LOCAL FOR Select SG_ID from TBL_SUPPLIERS_GOODS WHERE SG_CUR_ID = @l_iCUR_ID FOR UPDATE;
OPEN cSG;
FETCH NEXT FROM cSG INTO @l_iSG_ID;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
UPDATE TBL_SUPPLIERS_GOODS
SET SG_CUR_ID = @l_iCUR_ID
WHERE SG_ID = @l_iSG_ID AND SG_CUR_ID = @l_iCUR_ID;
FETCH NEXT FROM cSG INTO @l_iSG_ID;
END;
CLOSE cSG;
DEALLOCATE cSG;
IF IsNull(@l_fCUR_RATE, 0) <> IsNull((select CUR_RATE from Deleted where CUR_ID = @l_iCUR_ID), 0)
BEGIN
INSERT INTO TBL_CURRENCY_RATES (CURR_CUR_ID, CURR_RATE, CURR_DATE)
SELECT @l_iCUR_ID, @l_fCUR_RATE, GetDate();
END;
FETCH NEXT FROM cInserted INTO @l_iCUR_ID, @l_fCUR_RATE;
END;
CLOSE cInserted;
DEALLOCATE cInserted;
END;
|
