LISTING 1: Refresh Process That Updates Data from DB2/390 to SQL Server

-- Create the log table. (Switched bit to char(1) value;
-- switched datetime to timestamp.)
CREATE TABLE Prod_log
( lsn         int         generated always as IDENTITY,
  log_date    timestamp   NOT NULL with DEFAULT,
  tran_type   CHAR(1)     NOT NULL CHECK(tran_type IN('I', 'U', 'D')),
  productid   INT         NOT NULL,
  productname VARCHAR(25) NOT NULL,
  package     INT         NOT NULL,
  namechg     char(1)  not null with default 'N',
  packagechg  char(1)  not null with default 'N',
  primary key(lsn));

CREATE UNIQUE INDEX xprod_log
          ON Prod_Log
            (lsn ASC)
          BUFFERPOOL BP4;

-- Create source table. (BP4 is where we put index spaces
-- for Bufferpools. This isn't needed.)
CREATE TABLE Products
( productid   INT         NOT NULL,
  productname VARCHAR(25) NOT NULL,
  package     INT         NOT NULL,
  primary key(productid)
 );

CREATE UNIQUE INDEX xproducts
          ON Products
            (productid ASC)
          BUFFERPOOL BP4;

-- Insert Trigger
CREATE TRIGGER ti_prod AFTER INSERT ON Products
referencing new AS inserted
FOR EACH ROW MODE DB2SQL
  INSERT INTO Prod_log(tran_type, productid, productname, package)
  values ('I', inserted.productid, inserted.productname, inserted.package);

-- Delete Trigger
CREATE TRIGGER td_prod AFTER DELETE ON Products
referencing old AS inserted
FOR EACH ROW MODE DB2SQL
  INSERT INTO Prod_log(tran_type, productid, productname, package)
  values ('D', inserted.productid, inserted.productname, inserted.package);

-- Update Triggers
CREATE TRIGGER tu_prod1 AFTER UPDATE of productid ON Products
REFERENCING OLD AS oldrow NEW AS newrow
FOR EACH ROW MODE DB2SQL
WHEN (newrow.productid <> oldrow.productid)
   SIGNAL SQLSTATE '75001' ('Updates to productid are not allowed.');

CREATE TRIGGER tu_prod2 AFTER UPDATE of productname, package ON Products
REFERENCING OLD AS old_value NEW AS newrow
FOR EACH ROW MODE DB2SQL
 INSERT INTO Prod_log
             (tran_type, productid, productname, package,
                namechg, packagechg)
                             values( 'U',
                                 newrow.productid,
                                 newrow.productname,
                                 newrow.package,
                        CASE
                         WHEN newrow.productname <> old_value.productname
THEN 'Y'
                              ELSE 'N'
                           END,
                        CASE
                         WHEN newrow.package <> old_value.package THEN 'Y'
                              ELSE 'N'
                         END);

grant select, insert, update, delete ON products to public;
grant select, insert, update, delete ON prod_log to public;

-- Test run
DELETE FROM prod_log;

INSERT INTO Products(productid, productname, package)
  VALUES(105, 'prod5', 10);
INSERT INTO Products(productid, productname, package)
  VALUES(106, 'prod6', 10);
INSERT INTO Products(productid, productname, package)
  VALUES(107, 'prod7', 10);

DELETE FROM products;

INSERT INTO Products(productid, productname, package)
  VALUES(111, 'prod11', 10);

UPDATE products
SET package = 20
WHERE productid = 111;

-- Should fail
UPDATE products
SET productid = 110
WHERE productid = 111;

-- Look at what is left.
SELECT * FROM Products;
SELECT * FROM prod_log;