So who really wins?

Well I guess the answer would be no one (or the last one, or the one who bribes the dba...)

In any case, it was an exercise I alway contemplated, and always thought wasn't really a good idea, but always seems to come up.  I guess I should start a list of PROS and CONS for this type of operation.  Anyway the code was fun, and I hope it helps.  Not rocket science, and I betcha there could be a bunch of problems.  I tried to take in to account if the tables got out of sync or not.  Anyway, this was all spawned from this thread.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=40203

Here's the code:

SET NOCOUNT ON
GO

USE Northwind
GO
CREATE TABLE myTable99 (
   Col1 int NOT NULL PRIMARY KEY
 , Col2 char(1)
 , ModifiedBy varchar(25) NOT NULL
 , ModifiedDate Datetime NOT NULL DEFAULT GetDate())
GO

USE Pubs
GO
CREATE TABLE myTable99 (
   Col1 int NOT NULL PRIMARY KEY
 , Col2 char(1)
 , ModifiedBy varchar(25) NOT NULL
 , ModifiedDate Datetime NOT NULL DEFAULT GetDate())
GO

CREATE TRIGGER myTrigger99 ON myTable99
FOR INSERT, UPDATE, DELETE
AS

IF EXISTS (SELECT * FROM inserted) AND NOT EXISTS (SELECT * FROM deleted) -- INSERT
  BEGIN
  -- This should never execute if the tables are in synch
      UPDATE l
  SET Col2 = r.Col2, ModifiedBy = 'TRIGGER', ModifiedDate = GetDate()
        FROM Northwind.dbo.myTable99 l
        JOIN inserted r
   ON l.Col1 = r.Col1
  AND r.ModifiedBy <> 'TRIGGER'

  -- The need for the JOIN should be not required in the tables are in synch
 INSERT INTO Northwind.dbo.myTable99(Col1, Col2, ModifiedBy)
      SELECT l.Col1, l.Col2, 'TRIGGER' FROM inserted l
   LEFT JOIN Northwind.dbo.myTable99 r
   ON l.Col1 = r.Col1
       WHERE r.Col1 IS NULL
  AND l.ModifiedBy <> 'TRIGGER'
  END

IF NOT EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted) -- DELETE
  BEGIN

 DELETE Northwind.dbo.myTable99
 WHERE Col1 IN (SELECT Col1 FROM deleted
--   WHERE ModifiedBy <> 'TRIGGER'
        )
  END

IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted) -- UPDATE
  BEGIN
 UPDATE l
    SET Col2 = r.Col2, ModifiedBy = 'TRIGGER', ModifiedDate = GetDate()
  FROM Northwind.dbo.myTable99 l
  JOIN inserted r
    ON l.Col1 = r.Col1
   AND r.ModifiedBy <> 'TRIGGER'
  END
GO

USE Northwind
GO

CREATE TRIGGER myTrigger99 ON myTable99
FOR INSERT, UPDATE, DELETE
AS

IF EXISTS (SELECT * FROM inserted) AND NOT EXISTS (SELECT * FROM deleted) -- INSERT
  BEGIN
 UPDATE l
    SET Col2 = r.Col2, ModifiedBy = 'TRIGGER', ModifiedDate = GetDate()
  FROM Pubs.dbo.myTable99 l
  JOIN inserted r
    ON l.Col1 = r.Col1
   AND r.ModifiedBy <> 'TRIGGER'
 
 INSERT INTO Pubs.dbo.myTable99(Col1, Col2, ModifiedBy)
      SELECT l.Col1, l.Col2, 'TRIGGER' FROM inserted l
       LEFT JOIN Pubs.dbo.myTable99 r
       ON l.Col1 = r.Col1
    WHERE r.Col1 IS NULL
             AND l.ModifiedBy <> 'TRIGGER'
  END

IF NOT EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted) -- DELETE
  BEGIN
 DELETE Pubs.dbo.myTable99
  WHERE Col1 IN (SELECT Col1 FROM deleted
--    WHERE ModifiedBy <> 'TRIGGER'
   )
  END

IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted) -- UPDATE
  BEGIN
 UPDATE l
    SET Col2 = r.Col2, ModifiedBy = 'TRIGGER', ModifiedDate = GetDate()
  FROM Pubs.dbo.myTable99 l
  JOIN inserted r
    ON l.Col1 = r.Col1
   AND r.ModifiedBy <> 'TRIGGER'
  END
GO

INSERT INTO Northwind.dbo.myTable99(Col1, Col2, ModifiedBy)
SELECT 1, 'A', 'Brett' UNION ALL
SELECT 2, 'B', 'Brett' UNION ALL
SELECT 3, 'C', 'Brett'
GO

SELECT * FROM Northwind.dbo.myTable99
SELECT * FROM Pubs.dbo.myTable99
GO

UPDATE Northwind.dbo.myTable99 SET Col2='X', ModifiedBy = 'BrettK', ModifiedDate = GetDate()
 WHERE Col1 = 2
GO

SELECT * FROM Northwind.dbo.myTable99
SELECT * FROM Pubs.dbo.myTable99
GO

INSERT INTO Pubs.dbo.myTable99(Col1, Col2, ModifiedBy)
SELECT 4, 'D', 'Brett'
GO

SELECT * FROM Northwind.dbo.myTable99
SELECT * FROM Pubs.dbo.myTable99
GO

DELETE FROM Pubs.dbo.myTable99 WHERE Col1 = 1
GO

SELECT * FROM Northwind.dbo.myTable99
SELECT * FROM Pubs.dbo.myTable99
GO

DELETE FROM Northwind.dbo.myTable99 WHERE Col1 = 4
GO

SELECT * FROM Northwind.dbo.myTable99
SELECT * FROM Pubs.dbo.myTable99
GO

UPDATE Northwind.dbo.myTable99 SET Col2='X', ModifiedBy = 'BrettX', ModifiedDate = GetDate()
 WHERE Col1 = 3
GO

SELECT * FROM Northwind.dbo.myTable99
SELECT * FROM Pubs.dbo.myTable99
GO

USE Northwind
DROP TRIGGER myTrigger99
DROP TABLE myTable99
GO
USE Pubs
DROP TRIGGER myTrigger99
DROP TABLE myTable99
GO
SET NOCOUNT OFF
GO