CREATE TRIGGER [TRIGGER_NAME] ON [dbo].[TBL_NAME]
FOR INSERT, UPDATE, DELETE
AS

DECLARE @strAction AS VARCHAR(10)
DECLARE @STRTABLENAME AS VARCHAR (100)
DECLARE @STRCMD AS NVARCHAR (1000), @STRCMD1 AS NVARCHAR (1000)
DECLARE @Column_Name AS VARCHAR (50), @Type_Name AS VARCHAR (50)
DECLARE @DeletedRows AS INT
DECLARE @InsertedRows AS INT
DECLARE @nIndex AS INT
DECLARE @bContinue AS bit
SET @bContinue = 0

SELECT * INTO #inserted FROM inserted
SET @InsertedRows = @@rowcount


SELECT * INTO #deleted FROM deleted
SET @DeletedRows = @@rowcount

IF @InsertedRows > 0
BEGIN
IF @DeletedRows > 0
SET @strAction = 'UPDATE'
ELSE
SET @strAction = 'INSERT'
END
ELSE
BEGIN
SET @strAction = 'DELETE'
END

-- DELETE
IF 'DELETE' = @strAction
BEGIN
-- daca nu a mai fost apelat
IF ( (SELECT trigger_nestlevel(object_ID('TRIGGER_NAME')) )
BEGIN
SET @STRCMD = ' delete from SRVREPL_NAME.DBREPL_NAME.dbo.TBL_NAME where ID_NAME in (select ID_NAME from #deleted)'
EXECUTE(@STRCMD)
END
END

-- daca este insert sau update ... determin daca sa merg mai departe ...
-- si prelucrez
IF 'INSERT' = @strAction OR 'UPDATE' = @strAction
BEGIN

-- daca este insert sau update - determin care este valoarea lui SINCFIELD
DECLARE @SERVERNAME AS VARCHAR(20)
SELECT @SERVERNAME = SINCFIELD FROM #inserted

-- daca serverul din campul SINCFIELD este SRVREPL_NAME nu fac nimic
-- daca este NULL inseamna ca este prima data
-- daca e alt server ... merg mai departe
IF '['+@SERVERNAME+']' <> 'SRVREPL_NAME' OR @SERVERNAME IS NULL
BEGIN

-- setez ca o sa fac insert sau update
SET @bContinue = 1
-- daca este null ... il initializez cu numele serverului meu
IF @SERVERNAME IS NULL
BEGIN
UPDATE #inserted SET SINCFIELD = @@servername
END
END
-- daca SINCFIELD nu e NULL
IF NOT @SERVERNAME IS NULL
BEGIN
-- setez in tabela locala campul SINCFIELD pe null
UPDATE TBL_NAME SET SINCFIELD = NULL FROM TBL_NAME, #inserted WHERE TBL_NAME.ID_NAME = #inserted.ID_NAME
END

END


-- INSERT
IF 'INSERT' = @strAction AND @bContinue = 1
BEGIN
-- inserez
SET @STRCMD = ' INSERT INTO SRVREPL_NAME.DBREPL_NAME.dbo.TBL_NAME select FIELDS_LIST from #inserted '
EXECUTE(@STRCMD)
END

-- UPDATE
IF 'UPDATE' = @strAction AND @bContinue = 1
BEGIN

DECLARE @ColumnID int, @Columns nvarchar(4000), @ObjectID int, @LastColumnID int

SET @ObjectID=(SELECT id FROM sysobjects WHERE name='TBL_NAME')
SET @LastColumnID=(SELECT MAX(colid) FROM syscolumns WHERE id=@ObjectID)
SET @ColumnID=1
WHILE @ColumnID
IF (SUBSTRING(COLUMNS_UPDATED(),(@ColumnID - 1) / 8 + 1, 1)) &
POWER(2, (@ColumnID - 1) % 8) = POWER(2, (@ColumnID - 1) % 8)
SET @Columns = ISNULL(@Columns+',','') + COL_NAME(@ObjectID,@ColumnID) + ' = #inserted.' + COL_NAME(@ObjectID,@ColumnID) + ' '
SET @ColumnID=@ColumnID+1
END

-- trebuie sa trimit mai departe si SINCFIELD
SET @Columns = @Columns + ', SINCFIELD=#inserted.SINCFIELD '
-- trebuie sa inlocuiesc IN tabela tinta ... coloana @Column_Name
SET @STRCMD = 'UPDATE SRVREPL_NAME.DBREPL_NAME.dbo.TBL_NAME SET ' + @Columns + ' FROM SRVREPL_NAME.DBREPL_NAME.dbo.TBL_NAME C, #inserted WHERE C.ID_NAME = #inserted.ID_NAME'
EXECUTE(@STRCMD)


END

-- curatenie
DROP TABLE #inserted
DROP TABLE #DELETED




conditii ... fiecare tabela trebuie sa aiba campul SINCFIELD care sa accepte NULL si sa nu fie modificat din aplicatie.


ideea e ca la primul apel ea vine null si apoi trece la urmatoarele servere cu numele serverului care a initiat actiunea.

oricum pana la varianta finala care sa contina si offline mai e muuuuuuuuuuuuult ...

---

www.fagadar.ro