WEB LISTING 1: INSTEAD OF Triggers on the Orders View in Node 2 and Node 3

-- Run on Node2.
-- INSTEAD OF INSERT trigger.
CREATE TRIGGER trg_i_orders ON Orders INSTEAD OF INSERT
AS

-- First make sure that all of the data meets the CHECK constraint.
IF EXISTS(SELECT *
          FROM inserted
          WHERE customerid < 'A'
             OR customerid > 'ZZZZZ')
BEGIN
  RAISERROR('Trying to insert illegal customer ids. Transaction rolled back', 16, 1)
  ROLLBACK TRANSACTION
END
ELSE
BEGIN
  
  BEGIN DISTRIBUTED TRANSACTION

  -- Insert rows to the OrdersAF table.
  IF EXISTS(SELECT *
            FROM inserted
            WHERE customerid BETWEEN 'AAAAA' AND 'FZZZZ')
    INSERT INTO Node1.testdb.dbo.OrdersAF
      SELECT * FROM inserted
      WHERE customerid BETWEEN 'AAAAA' AND 'FZZZZ'

  -- Insert rows to the OrdersGP table.
  IF EXISTS(SELECT *
            FROM inserted
            WHERE customerid BETWEEN 'GAAAA' AND 'PZZZZ')
    INSERT INTO OrdersGP -- local
      SELECT * FROM inserted
      WHERE customerid BETWEEN 'GAAAA' AND 'PZZZZ'

  -- Insert rows to the OrdersQZ table.
  IF EXISTS(SELECT *
            FROM inserted
            WHERE customerid BETWEEN 'QAAAA' AND 'ZZZZZ')
    INSERT INTO Node3.testdb.dbo.OrdersQZ
      SELECT * FROM inserted
      WHERE customerid BETWEEN 'QAAAA' AND 'ZZZZZ'

  COMMIT TRANSACTION

END    
GO

-- INSTEAD OF DELETE trigger
CREATE TRIGGER trg_d_orders ON Orders INSTEAD OF DELETE
AS

BEGIN DISTRIBUTED TRANSACTION

-- Delete rows from the OrdersAF table.
IF EXISTS(SELECT *
          FROM deleted
          WHERE customerid BETWEEN 'AAAAA' AND 'FZZZZ')
  DELETE FROM Node1.testdb.dbo.OrdersAF
  FROM Node1.testdb.dbo.OrdersAF AS O JOIN deleted AS D
    ON  O.orderid = D.orderid
    AND O.customerid BETWEEN 'AAAAA' AND 'FZZZZ'

-- Delete rows from the OrdersGP table.
IF EXISTS(SELECT *
          FROM deleted
          WHERE customerid BETWEEN 'GAAAA' AND 'PZZZZ')
  DELETE FROM OrdersGP -- local
  FROM OrdersGP AS O JOIN deleted AS D
    ON  O.orderid = D.orderid
    AND O.customerid BETWEEN 'GAAAA' AND 'PZZZZ'

-- Delete rows from the OrdersQZ table.
IF EXISTS(SELECT *
          FROM deleted
          WHERE customerid BETWEEN 'QAAAA' AND 'ZZZZZ')
  DELETE FROM Node3.testdb.dbo.OrdersQZ
  FROM Node3.testdb.dbo.OrdersQZ AS O JOIN deleted AS D
    ON  O.orderid = D.orderid
    AND O.customerid BETWEEN 'QAAAA' AND 'ZZZZZ'

COMMIT TRANSACTION
GO

-- INSTEAD OF UPDATE trigger.
CREATE TRIGGER trg_u_orders ON Orders INSTEAD OF UPDATE
AS

-- Make sure that all of the data meets the CHECK constraint.
IF EXISTS(SELECT *
          FROM inserted
          WHERE customerid < 'A'
             OR customerid > 'ZZZZZ')
BEGIN
  RAISERROR('Trying to insert illegal customer ids. Transaction rolled back', 16, 1)
  ROLLBACK TRANSACTION
END
ELSE
BEGIN
  
  BEGIN DISTRIBUTED TRANSACTION

  -- Delete rows from the OrdersAF table.
  IF EXISTS(SELECT *
            FROM deleted
            WHERE customerid BETWEEN 'AAAAA' AND 'FZZZZ')
    DELETE FROM Node1.testdb.dbo.OrdersAF
    FROM Node1.testdb.dbo.OrdersAF AS O JOIN deleted AS D
      ON  O.orderid = D.orderid
      AND O.customerid BETWEEN 'AAAAA' AND 'FZZZZ'

  -- Delete rows from the OrdersGP table.
  IF EXISTS(SELECT *
            FROM deleted
            WHERE customerid BETWEEN 'GAAAA' AND 'PZZZZ')
    DELETE FROM OrdersGP -- local
    FROM OrdersGP AS O JOIN deleted AS D
      ON  O.orderid = D.orderid
      AND O.customerid BETWEEN 'GAAAA' AND 'PZZZZ'

  -- Delete rows from the OrdersQZ table.
  IF EXISTS(SELECT *
            FROM deleted
            WHERE customerid BETWEEN 'QAAAA' AND 'ZZZZZ')
    DELETE FROM Node3.testdb.dbo.OrdersQZ
    FROM Node3.testdb.dbo.OrdersQZ AS O JOIN deleted AS D
      ON  O.orderid = D.orderid
      AND O.customerid BETWEEN 'QAAAA' AND 'ZZZZZ'

  -- Insert rows to the OrdersAF table.
  IF EXISTS(SELECT *
            FROM inserted
            WHERE customerid BETWEEN 'AAAAA' AND 'FZZZZ')
    INSERT INTO Node1.testdb.dbo.OrdersAF
      SELECT * FROM inserted
      WHERE customerid BETWEEN 'AAAAA' AND 'FZZZZ'

  -- Insert rows to the OrdersGP table.
  IF EXISTS(SELECT *
            FROM inserted
            WHERE customerid BETWEEN 'GAAAA' AND 'PZZZZ')
    INSERT INTO OrdersGP -- local
      SELECT * FROM inserted
      WHERE customerid BETWEEN 'GAAAA' AND 'PZZZZ'

  -- Insert rows to the OrdersQZ table.
  IF EXISTS(SELECT *
            FROM inserted
            WHERE customerid BETWEEN 'QAAAA' AND 'ZZZZZ')
    INSERT INTO Node3.testdb.dbo.OrdersQZ
      SELECT * FROM inserted
      WHERE customerid BETWEEN 'QAAAA' AND 'ZZZZZ'

  COMMIT TRANSACTION

END    
GO

-- Run on Node3.
-- INSTEAD OF INSERT trigger
CREATE TRIGGER trg_i_orders ON Orders INSTEAD OF INSERT
AS

-- First make sure that all of the data meets the CHECK constraint.
IF EXISTS(SELECT *
          FROM inserted
          WHERE customerid < 'A'
             OR customerid > 'ZZZZZ')
BEGIN
  RAISERROR('Trying to insert illegal customer ids. Transaction rolled back', 16, 1)
  ROLLBACK TRANSACTION
END
ELSE
BEGIN
  
  BEGIN DISTRIBUTED TRANSACTION

  -- Insert rows to the OrdersAF table.
  IF EXISTS(SELECT *
            FROM inserted
            WHERE customerid BETWEEN 'AAAAA' AND 'FZZZZ')
    INSERT INTO Node1.testdb.dbo.OrdersAF
      SELECT * FROM inserted
      WHERE customerid BETWEEN 'AAAAA' AND 'FZZZZ'

  -- Insert rows to the OrdersGP table.
  IF EXISTS(SELECT *
            FROM inserted
            WHERE customerid BETWEEN 'GAAAA' AND 'PZZZZ')
    INSERT INTO Node2.testdb.dbo.OrdersGP
      SELECT * FROM inserted
      WHERE customerid BETWEEN 'GAAAA' AND 'PZZZZ'

  -- Insert rows to the OrdersQZ table.
  IF EXISTS(SELECT *
            FROM inserted
            WHERE customerid BETWEEN 'QAAAA' AND 'ZZZZZ')
    INSERT INTO OrdersQZ -- local
      SELECT * FROM inserted
      WHERE customerid BETWEEN 'QAAAA' AND 'ZZZZZ'

  COMMIT TRANSACTION

END    
GO

-- INSTEAD OF DELETE trigger.
CREATE TRIGGER trg_d_orders ON Orders INSTEAD OF DELETE
AS

BEGIN DISTRIBUTED TRANSACTION

-- Delete rows from the OrdersAF table.
IF EXISTS(SELECT *
          FROM deleted
          WHERE customerid BETWEEN 'AAAAA' AND 'FZZZZ')
  DELETE FROM Node1.testdb.dbo.OrdersAF
  FROM Node1.testdb.dbo.OrdersAF AS O JOIN deleted AS D
    ON  O.orderid = D.orderid
    AND O.customerid BETWEEN 'AAAAA' AND 'FZZZZ'

-- Delete rows from the OrdersGP table.
IF EXISTS(SELECT *
          FROM deleted
          WHERE customerid BETWEEN 'GAAAA' AND 'PZZZZ')
  DELETE FROM Node2.testdb.dbo.OrdersGP
  FROM Node2.testdb.dbo.OrdersGP AS O JOIN deleted AS D
    ON  O.orderid = D.orderid
    AND O.customerid BETWEEN 'GAAAA' AND 'PZZZZ'

-- Delete rows from the OrdersQZ table.
IF EXISTS(SELECT *
          FROM deleted
          WHERE customerid BETWEEN 'QAAAA' AND 'ZZZZZ')
  DELETE FROM OrdersQZ -- local
  FROM OrdersQZ AS O JOIN deleted AS D
    ON  O.orderid = D.orderid
    AND O.customerid BETWEEN 'QAAAA' AND 'ZZZZZ'

COMMIT TRANSACTION
GO

-- INSTEAD OF UPDATE trigger
CREATE TRIGGER trg_u_orders ON Orders INSTEAD OF UPDATE
AS

-- Make sure that all of the data meets the CHECK constraint.
IF EXISTS(SELECT *
          FROM inserted
          WHERE customerid < 'A'
             OR customerid > 'ZZZZZ')
BEGIN
  RAISERROR('Trying to insert illegal customer ids. Transaction rolled back', 16, 1)
  ROLLBACK TRANSACTION
END
ELSE
BEGIN
  
  BEGIN DISTRIBUTED TRANSACTION

  -- Delete rows from the OrdersAF table.
  IF EXISTS(SELECT *
            FROM deleted
            WHERE customerid BETWEEN 'AAAAA' AND 'FZZZZ')
    DELETE FROM Node1.testdb.dbo.OrdersAF
    FROM Node1.testdb.dbo.OrdersAF AS O JOIN deleted AS D
      ON  O.orderid = D.orderid
      AND O.customerid BETWEEN 'AAAAA' AND 'FZZZZ'

  -- Delete rows from the OrdersGP table.
  IF EXISTS(SELECT *
            FROM deleted
            WHERE customerid BETWEEN 'GAAAA' AND 'PZZZZ')
    DELETE FROM Node2.testdb.dbo.OrdersGP
    FROM Node2.testdb.dbo.OrdersGP AS O JOIN deleted AS D
      ON  O.orderid = D.orderid
      AND O.customerid BETWEEN 'GAAAA' AND 'PZZZZ'

  -- Delete rows from the OrdersQZ table.
  IF EXISTS(SELECT *
            FROM deleted
            WHERE customerid BETWEEN 'QAAAA' AND 'ZZZZZ')
    DELETE FROM OrdersQZ -- local
    FROM OrdersQZ AS O JOIN deleted AS D
      ON  O.orderid = D.orderid
      AND O.customerid BETWEEN 'QAAAA' AND 'ZZZZZ'

  -- Insert rows to the OrdersAF table.
  IF EXISTS(SELECT *
            FROM inserted
            WHERE customerid BETWEEN 'AAAAA' AND 'FZZZZ')
    INSERT INTO Node1.testdb.dbo.OrdersAF
      SELECT * FROM inserted
      WHERE customerid BETWEEN 'AAAAA' AND 'FZZZZ'

  -- Insert rows to the OrdersGP table.
  IF EXISTS(SELECT *
            FROM inserted
            WHERE customerid BETWEEN 'GAAAA' AND 'PZZZZ')
    INSERT INTO Node2.testdb.dbo.OrdersGP
      SELECT * FROM inserted
      WHERE customerid BETWEEN 'GAAAA' AND 'PZZZZ'

  -- Insert rows to the OrdersQZ table.
  IF EXISTS(SELECT *
            FROM inserted
            WHERE customerid BETWEEN 'QAAAA' AND 'ZZZZZ')
    INSERT INTO OrdersQZ -- local
      SELECT * FROM inserted
      WHERE customerid BETWEEN 'QAAAA' AND 'ZZZZZ'

  COMMIT TRANSACTION

END    
GO