T-SQL

Re: trigger de INSERT,UPDATE - SQL Server 2000

crestinul — Tue, 26 Jun 2007 09:31:25 GMT

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

Re: trigger de INSERT,UPDATE - SQL Server 2000

Alex — Tue, 26 Jun 2007 06:31:37 GMT

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.

Re: trigger de INSERT,UPDATE - SQL Server 2000

Diana — Mon, 25 Jun 2007 19:35:52 GMT

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.

Re: trigger de INSERT,UPDATE - SQL Server 2000

Alex — Mon, 25 Jun 2007 18:38:34 GMT

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

Re: trigger de INSERT,UPDATE - SQL Server 2000

Alex — Mon, 25 Jun 2007 18:27:24 GMT

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.

Re: trigger de INSERT,UPDATE - SQL Server 2000

Alex — Mon, 25 Jun 2007 14:01:14 GMT

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

Re: trigger de INSERT,UPDATE - SQL Server 2000

Alex — Mon, 25 Jun 2007 13:25:45 GMT

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.

Re: trigger de INSERT,UPDATE - SQL Server 2000

Alex — Mon, 25 Jun 2007 12:20:38 GMT

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

alex.

trigger de INSERT,UPDATE - SQL Server 2000

Alex — Mon, 25 Jun 2007 11:43:10 GMT

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.