Bi-Directional Updates

Posts: 83
Comments: 600
Trackbacks: 40

<< Bonnie And Charly And Frances And Ivan And ? | Home | SQL SERVER TOP 10 >>

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