salut, am 3 baze de date pe acelasi server.....fiecare baza de date contine tabela aceasta:

CREATE TABLE [dbo].[EQUIVAL] (
 [CURIDREF] [int] NOT NULL ,
 [RATEDATE] [datetime] NOT NULL ,
 [CURID] [int] NOT NULL ,
 [PURCHASERATE] [float] NULL ,
 [FIXINGRATE] [float] NULL ,
 [SALESRATE] [float] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[EQUIVAL] WITH NOCHECK ADD
 CONSTRAINT [PK_EQUIVAL] PRIMARY KEY  CLUSTERED
 (
  [CURIDREF],
  [RATEDATE],
  [CURID]
 ) WITH  FILLFACTOR = 90  ON [PRIMARY]
GO

vreau sa fac un trigger de INSERT,UPDATE care sa-l pun pe fiecare baza de date(pe tabela de mai sus).....atunci cand modific in tabela de mai sus pe oricare din cele 3 baze de date sa se modifice si pe celelalte 2.......sa verifice dupa PK si daca nu exista sa faca INSERT daca exista sa faca UPDATE..............ca sa nu intre in bucla infinita(triggerul fiind pe toate cele 3 baze de date) am inteles ca pot folosi:

 "if trigger_nestlevel(object_id(N'[dbo].[Nume_Trigger]'))=1" la inceputul triggerului dupa AS ca sa opreasca bucla infinita....adica triggerul fire pe batabase A face modificrile pe celelalte 2 server sa zicem B si C si se opreste cand ajunge inapoi la A

ma puteti ajuta va rog cu acest trigger......cum ar trebui sa arate....

multumesc mult. 

alex.

ceva este gresit....dar nu-mi dau seama ce......ce ar mai trebui facut?...ceva sugestii?....daca fac un insert pe tabela respectiva imi da o eroare

"Server: Msg 925, Level 19, State 1, Procedure trg_Equival_INSERT_UPDATE, Line 14
Maximum number of databases used for each query has been exceeded. The maximum allowed is 8.

Connection Broken"

asta e triggerul...asa cum m-am priceput eu :)

CREATE TRIGGER [trg_Equival_INSERT_UPDATE] ON [dbo].[EQUIVAL]
FOR INSERT,UPDATE
AS

if trigger_nestlevel(object_id(N'[dbo].[trg_Equival_INSERT_UPDATE]'))=1
--if @@rowcount = 0 return
declare @CURIDREF as int ,
 @RATEDATE as datetime ,
 @CURID as int

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

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

 begin
  insert into DBName..EQUIVAL (curidref,ratedate,curid,purchaserate,fixingrate,salesrate)
  select curidref,ratedate,curid,purchaserate,fixingrate,salesrate from inserted
  
  -- se mai adauga inca un database

 end
else
 begin
  update a
  set a.purchaserate = i.purchaserate, a.fixingrate = i.fixingrate, a.salesrate = i.salesrate
  from DBName..EQUIVAL a, inserted i
  where a.curidref = @curidref and a.ratedate = @ratedate and a.curid = @curid
 
  -- se mai adauga inca un database

 end

GO

alex.

 

se pare ca trebuia asa la trigger_nestlevel:

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

 

oricum sunt sigur ca poate fi imbunatatit sau ca nu iau in calcul tot...simt ca-i lipseste ceva...:)

alex.

ba nu....nu are treaba....vad ca acum functioneaza cum trebuie in ambele variante...era de la un alt trigger :)

dar mai am o problema....primesc eroarea

"Server: Msg 925, Level 19, State 1, Procedure trg_Equival_INSERT_UPDATE, Line 15
Maximum number of databases used for each query has been exceeded. The maximum allowed is 8.

Connection Broken"

daca am 2 database.....A si B...si pun trigger pe A (Insert, Update) sa faca pe B...si pun si trigger pe B (Insert,Update) sa faca pe A....deci sa sincronizze cele 2 tabele....merge ok....daca mai introduc si un al 3-lea database C nu mai e ok ca-mi da eroarea de mai sus

deci daca am bucla de 3 si nu de 2 databaseuri nu mai merge....si nu stiu de ce? :(

 

alex.

 o problema asemanatoare am gasit si la

http://www.developmentnow.com/g/113_2004_9_0_0_432446/errorurgent--Maximum-number-of-databases-used-for-each-query-has-been-exceeded.htm

dar nu si solutia...sau cel putin eu nu o inteleg 

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. 

 

ce mai vorbesc singur pe aici :)))))))))))).....dar tot e bine ca se rezolva lucrurile :)

alex. 

 

Edit...am gasit varianta cea mai simpla.... ca de obicei varianta cea mai buna si simpla o "vad" ultima :)

CREATE TRIGGER [trg_Equival_INSERT_UPDATE] ON [dbo].[EQUIVAL]
FOR INSERT,UPDATE
AS

if ((select trigger_nestlevel(object_id(N'[dbo].[trg_Equival_INSERT_UPDATE]')))=1)
BEGIN
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
else
     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
END
GO 

Ce incerci sa faci seamana cu o replicare "merge"...

* Din pacate replicarea este unul din punctele mele cam slabe in momentul asta ...deci nu pot sa merg mai in amanunt acum, dar cred ca merita sa abordezi problema astfel.

si la mine "replicarea" e cam zero.....inafara de putina teorie altceva nu mai stiu legat de asta......dar poate e momentul sa invat :)....o sa ma gandesc si la varianta asta......bine macar ca o varianta, chiar si asa "facuta pe genunchi", functioneaza si nu-mi sta in carca...:)

multumesc.

alex.

Daca ai sql2005 ai putea folosi service broker folosind mecanismul de queue si xml,vezi pe blogul lui Remus Rusanu

---

Secolul XXI ori va fi religios ori nu va fi deloc