Using BEFORE UPDATE triggers

In the following example, three tables store information about the interactions between sales representatives and customers: Customers, Contacts, and SalesReps. Each sales representative sells to several customers. For some customers, there is a single contact, and for other customers there are multiple contacts.

The Contacts table has a foreign key to the Customers table. The Customers table has a foreign key to the SalesReps table.

For detailed descriptions of the tables, see Description of Contacts, Customers, and SalesReps tables.

A sales representative subscribes to a publication that provides a copy of the SalesRep table, a copy of the Customers table with the details of the customers assigned to them, and a copy of the Contacts table with the details of the contacts that correspond to their customers. For example, each sales representative subscribes to the following publication:

CREATE PUBLICATION SalesRepData (
   TABLE SalesReps
   TABLE Customers
      SUBSCRIBE BY rep_key
   TABLE Contacts
      SUBSCRIBE BY (SELECT rep_key
         FROM Customers
         WHERE Contacts.cust_key = Customers.cust_key )
);

For a detailed description of this publication, see Using disjoint data partitions.

Maintaining referential integrity

This reassignment of rows among subscribers is sometimes called territory realignment because it is a common feature of sales force automation applications, where customers are periodically reassigned among representatives.

On the consolidated database, when a customer is reassigned to a new sales representative, the rep_key value in the Customers table is updated.

The following statement reassigns a customer, cust1, to another sales representative, rep2.

UPDATE Customers
SET rep_key = 'rep2'
WHERE cust_key = 'cust1';

This update is replicated:

As a DELETE statement to the Customers table on the old sales representative's remote database.
As an INSERT statement to the Customers table on the new sales representative's remote database.

The Contacts table is not changed. There are no entries in the consolidated database transaction log about the Contacts table. As a result, SQL Remote on the remote databases cannot reassign the cust_key rows of the Contacts table. This inability causes the following referential integrity problem: the Contacts table on the remote database of the old sales representative contains a cust_key value for which there is no longer a customer.

A solution is to use a BEFORE UPDATE trigger. A BEFORE UPDATE trigger does not make any change to the database tables, but does create an entry in the consolidated database transaction log.

This BEFORE UPDATE trigger must be fired:

Before the UPDATE statement is run, so that the BEFORE value of the row is evaluated and added to the transaction log.
FOR EACH ROW rather than for each statement. The information provided by the trigger must be the new subscription expression.

For example, the following statement creates a BEFORE UPDATE trigger.

CREATE TRIGGER "UpdateCustomer" BEFORE UPDATE OF "rep_key"
// only fire the trigger when rep_key is modified, not any other column
ORDER 1 ON "Cons"."Customers"
/* REFERENCING OLD AS old_name NEW AS new_name */
REFERENCING NEW AS NewRow
   OLD AS OldRow
FOR EACH ROW 
BEGIN
// determine the new subscription expression
   // for the Customers table
	 UPDATE Contacts
   PUBLICATION SalesRepData
   OLD SUBSCRIBE BY ( OldRow.rep_key )
   NEW SUBSCRIBE BY ( NewRow.rep_key )
   WHERE cust_key = NewRow.cust_key;
END
;

SQL Remote uses the information placed in the transaction log to determine which subscribers receive which rows.

The consolidated database transaction log contains two entries after this statement is executed:

INSERT and DELETE statements for the Contacts table generated by the BEFORE UPDATE trigger.

--BEGIN TRIGGER-1029-0000461705
--BEGIN TRANSACTION-1029-0000461708
BEGIN TRANSACTION
go
--UPDATE PUBLICATION-1029-0000461711 Cons.Contacts
--PUBLICATION-1029-0000461711-0002-NEW_SUBSCRIBE_BY-rep2
--PUBLICATION-1029-0000461711-0002-OLD_SUBSCRIBE_BY-rep1
--NEW-1029-0000461711
--INSERT INTO Cons.Contacts(contact_key,name,cust_key)
--VALUES ('5','Joe','cust1')
go
--OLD-1029-0000461711
--DELETE FROM Cons.Contacts
-- WHERE contact_key='5'
go
--END TRIGGER-1029-0000461743

The original UPDATE statement that was executed, as well as INSERT and DELETE statements for those users that gained or lost the row respectively.

--PUBLICATION-1029-0000461746-0002-NEW_SUBSCRIBE_BY-rep2
--PUBLICATION-1029-0000461746-0002-OLD_SUBSCRIBE_BY-rep1
--NEW-1029-0000461746
--INSERT INTO Cons.Customers(cust_key,name,rep_key)
--VALUES ('cust1','company1','rep2')
go
--OLD-1029-0000461746
--DELETE FROM Cons.Customers
-- WHERE cust_key='cust1'
go
--UPDATE-1029-0000461746
UPDATE Cons.Customers
   SET rep_key='rep2'
VERIFY (rep_key)
VALUES ('1')
 WHERE cust_key='cust1'
go
--COMMIT-1029-0000461785
COMMIT WORK

SQL Remote scans the transaction log for the BEFORE and AFTER tags. Based on this information, it can determine which remote users get an INSERT, UPDATE, or DELETE statement.

When a user is in the BEFORE list and not in the AFTER list, then a DELETE statement is sent on the Contacts table.
When a user is in the AFTER list and not the BEFORE list, then an INSERT statement is sent on the Contacts table.
When a user is in both the BEFORE and AFTER lists, nothing is done to the Contacts table but the UPDATE statement on the Customers table is sent.

When the BEFORE and AFTER lists are identical, the remote user already has the row and an UPDATE statement is sent.

Notes on the trigger

In the following example, you must use a BEFORE UPDATE trigger. In other contexts, BEFORE DELETE and BEFORE INSERT are necessary.

UPDATE table-name
PUBLICATION pub-name
   SUBSCRIBE BY sub-expression
WHERE search-condition;

In this example, you use a BEFORE trigger.

UPDATE table-name
PUBLICATION publication-name
   OLD SUBSCRIBE BY old-subscription-expression
   NEW SUBSCRIBE BY new-subscription-expression
WHERE search-condition;

The UPDATE statement lists the affected publication and table. The WHERE clause in the statement describes the affected rows. This UPDATE statement does not change the data in the table; it makes entries in the transaction log.

In this example, the subscription expression returns a single value. However, subqueries returning multiple values can also be used. The value of the subscription expression must be the value after the update.

In this example, the only subscriber to the row is the new sales representative. For an example of a row that has existing and new subscribers, see Using overlap partitions.

Discuss this page in DocCommentXchange.