Удаление лога редактирования товаров поставщика.
Код
set nocount on;
DECLARE @iS_ID int, @iSG_ID int, @iLOG_ID int, @sLOG_FIELD_NAME varchar(150);
DECLARE cSuppliers CURSOR LOCAL FOR
select
S_ID
from TBL_SUPPLIERS s
where s.S_ID = 1111
order by s.S_ID
;
OPEN cSuppliers;
FETCH NEXT FROM cSuppliers INTO @iS_ID;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
DECLARE cSuppliersGoods CURSOR LOCAL READ_ONLY FOR
select
SG_ID
from TBL_SUPPLIERS_GOODS sg
where sg.SG_S_ID = @iS_ID;
PRINT @iS_ID;
OPEN cSuppliersGoods;
FETCH NEXT FROM cSuppliersGoods INTO @iSG_ID;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
DECLARE cLog CURSOR LOCAL READ_ONLY FOR
select LOG_FIELD_NAME
from TBL_LOG l
where l.LOG_TBL_NAME = 'TBL_SUPPLIERS_GOODS'
and l.LOG_PK = @iSG_ID
and l.LOG_TYPE = 2
group by LOG_FIELD_NAME
having count(*) > 1;
OPEN cLog;
FETCH NEXT FROM cLog INTO @sLOG_FIELD_NAME;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
DECLARE cLogValues CURSOR LOCAL READ_ONLY FOR
select l.LOG_ID
from TBL_LOG l
where l.LOG_TBL_NAME = 'TBL_SUPPLIERS_GOODS'
and l.LOG_PK = @iSG_ID
and l.LOG_TYPE = 2
and l.LOG_FIELD_NAME = @sLOG_FIELD_NAME
and not l.LOG_ID in (
select TOP 1 l.LOG_ID
from TBL_LOG l
where l.LOG_TBL_NAME = 'TBL_SUPPLIERS_GOODS'
and l.LOG_PK = @iSG_ID
and l.LOG_TYPE = 2
and l.LOG_FIELD_NAME = @sLOG_FIELD_NAME
order by LOG_DATE DESC
)/* FOR UPDATE*/;
OPEN cLogValues;
FETCH NEXT FROM cLogValues INTO @iLOG_ID;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
DELETE FROM TBL_LOG
WHERE LOG_ID = @iLOG_ID;
FETCH NEXT FROM cLogValues INTO @iLOG_ID;
END;
CLOSE cLogValues;
DEALLOCATE cLogValues;
FETCH NEXT FROM cLog INTO @sLOG_FIELD_NAME;
END;
CLOSE cLog;
DEALLOCATE cLog;
FETCH NEXT FROM cSuppliersGoods INTO @iSG_ID;
END;
CLOSE cSuppliersGoods;
DEALLOCATE cSuppliersGoods;
FETCH NEXT FROM cSuppliers INTO @iS_ID;
END;
CLOSE cSuppliers;
DEALLOCATE cSuppliers;
В строке where s.S_ID = 1111 вместо кода 1111 укажите код вашего поставщика из справочника поставщиков.
|
