Листинг 6. Триггер для генерации первичного ключа

Insert:
CREATE  trigger ti_FE_POSITIONS on FE_POSITIONS INSTEAD OF INSERT as
DECLARE @NewId int;DECLARE @RowNumber int;DECLARE
 @CurrRow int;DECLARE @CurYear int;DECLARE @TableName varchar(128);
SET @NewId=0;SET @CurrRow=1; 
select distinct year(table_year) t_y into #MyTmp from inserted;
select * into #my_ins from inserted;
declare @CurYearVC varchar(4);
declare MyCurs cursor LOCAL for select t_y from #MyTmp;
select @RowNumber=count(*) from #MyTmp;
open MyCurs;
WHILE @CurrRow<=@RowNumber BEGIN
 FETCH NEXT FROM MyCurs into @CurYear; 
set @CurYearVC=convert(varchar(4),@CurYear);
 set @TableName = ?FE_POSITIONS_?+ @CurYearVC;
exec (?insert into ?+@TableName+? select * from #my_ins where
 year(table_year)=?+@CurYearVC);
if @@Error<>0 begin return; end;
SET @CurrRow=@CurrRow+1; END;
deallocate MyCurs;

update:
create  trigger tu_FE_POSITIONS on FE_POSITIONS INSTEAD OF UPDATE as
DECLARE @NewId int;DECLARE @RowNumber int;DECLARE @CurrRow int;DECLARE
 @CurYear int; declare @CurYearVC varchar(4);
DECLARE @TableName varchar(128);SET @NewId=0;SET @CurrRow=1; 
select * into #MyInserted from inserted;
select distinct year(table_year) t_y into #MyTmp from #myinserted;
declare MyCurs cursor LOCAL for select t_y from #MyTmp;
select @RowNumber=count(*) from #MyTmp;open MyCurs;
WHILE @CurrRow<=@RowNumber BEGIN
 FETCH NEXT FROM MyCurs into @CurYear; 
set @CurYearVC=convert(varchar(4),@CurYear);
set @TableName = ?FE_POSITIONS_?+@CurYearVC;
exec (?UPDATE ?+@TableName+? set AUTOPKID=i.AUTOPKID, chSellPrice=i.chSellPrice,
 COEF=i.COEF, Count=i.Count, COUNT2=i.COUNT2, countCrd=i.countCrd,
 crdCodeOld=i.crdCodeOld, createdate=i.createdate, createtime=i.createtime,
 fe_cardprod_id=i.fe_cardprod_id, fe_merch_id=i.fe_merch_id,
 fe_position_id=i.fe_position_id, Firm=i.Firm, Hacc=i.Hacc,
 iCalcStyle=i.iCalcStyle, isdeleted=i.isdeleted, M_U_ID_2=i.M_U_ID_2,
 modifydate=i.modifydate, modifytime=i.modifytime, mu_id=i.mu_id,
 n_d_id=i.n_d_id, NDS=i.NDS, opr=i.opr, party=i.party, Price=i.Price,
 PRICE2=i.PRICE2, PriceCrd=i.PriceCrd, section=i.section,
 SellPrice=i.SellPrice, Serial=i.Serial, site_id=i.site_id,
 Sum=i.Sum, SumCrd=i.SumCrd, table_year=i.table_year from
 #myinserted i where ? +@TableName + ?.FE_POSITION_ID=i.FE_POSITION_ID
 and i.FE_POSITION_ID in (select FE_POSITION_ID from
 #myinserted where year(table_year)=?+@CurYearVC+?)?);
if @@Error<>0 begin return; end;
SET @CurrRow=@CurrRow+1; END;
deallocate MyCurs;

delete:
create  trigger td_FE_POSITIONS on FE_POSITIONS INSTEAD OF DELETE as
DECLARE @NewId int;DECLARE @RowNumber int;DECLARE
 @CurrRow int;DECLARE @CurYear int;DECLARE @TableName varchar(128);
SET @NewId=0;SET @CurrRow=1; 
declare @CurYearVC varchar(4);
select * into #MyDeleted from deleted;
select distinct year(table_year) t_y into #MyTmp from #MyDeleted
declare MyCurs cursor LOCAL for select t_y from #MyTmp;
select @RowNumber=count(*) from #MyTmp;open MyCurs;
WHILE @CurrRow<=@RowNumber BEGIN
 FETCH NEXT FROM MyCurs into @CurYear; 
set @CurYearVC=convert(varchar(4),@CurYear);
set @TableName = ?FE_POSITIONS_?+@CurYearVC;
exec (?delete from ?+@TableName+? where FE_POSITION_ID in (select
 FE_POSITION_ID from #MyDeleted where year(table_year)=?+@CurYearVC+?)?);
if @@Error<>0 begin return; end;
SET @CurrRow=@CurrRow+1; END;
deallocate MyCurs;