am inteles care e problema dar nu si cum se rezolva....

 am impartit triggerul in 2....unul pt INSERT altul pt UPDATE

acum insertul merge struna dar la update inca nu stiu cum sa-l opresc sa nu intre in bucla infinita

INSERT-ul arata asa:

alter TRIGGER [trg_Equival_INSERT] ON [dbo].[EQUIVAL]
FOR INSERT
AS

if ((select trigger_nestlevel(object_id(N'[dbo].[trg_Equival_INSERT]')))=1)

declare @CURIDREF as int ,
 @RATEDATE as datetime ,
 @CURID as int

select @CURIDREF = curidref, @RATEDATE = ratedate, @CURID = curid from inserted

If Not Exists(SELECT * FROM A..EQUIVAL WHERE curidref=@CURIDREF AND
ratedate = @RATEDATE AND curid = @CURID)

     begin

      insert into A..EQUIVAL (curidref,ratedate,curid,purchaserate,fixingrate,salesrate)
      select curidref,ratedate,curid,purchaserate,fixingrate,salesrate from inserted
    
    end
GO

si merge f bine ca se oprreste la conditia if not exists

UPDATE-ul arata asa:

alter TRIGGER [trg_Equival_UPDATE] ON [dbo].[EQUIVAL]
FOR UPDATE
AS

if ((select trigger_nestlevel(object_id(N'[dbo].[trg_Equival_UPDATE]')))=1)


declare @CURIDREF as int ,
 @RATEDATE as datetime ,
 @CURID as int

select @CURIDREF = curidref, @RATEDATE = ratedate, @CURID = curid from inserted

If Exists(SELECT * FROM A..EQUIVAL WHERE curidref=@CURIDREF AND
ratedate = @RATEDATE AND curid = @CURID)

    begin

      update a
      set a.purchaserate = i.purchaserate, a.fixingrate = i.fixingrate, a.salesrate = i.salesrate
      from A..EQUIVAL a, inserted i
      where a.curidref = @curidref and a.ratedate = @ratedate and a.curid = @curid
 
    end

GO

dar nu stiu cum sa-l opresc....ca aici nu mai tine nici o smecherie :(

alex.

Edit: cred ca am reusit modoficand conditia la  UPDATE astfel:

If Exists(SELECT * FROM A..EQUIVAL WHERE curidref=@CURIDREF AND
ratedate = @RATEDATE AND curid = @CURID) AND ((select trigger_nestlevel(object_id(N'[dbo].[trg_Equival_UPDATE]')))=1)

alex.