Creating database trigger to synchronize employee category with company category

From LeonWiki!

Contents 1 Description 1.1 Business Rules 1.2 Strategy 1.3 Features 1.4 Remaining Issues 1.5 Installation 1.6 Testing Strategy 1.7 Benefits

Description

The goal is the synchronization of contact records' category value with the company records' category value. When the company record category changes, the contact records' category value should be updated to the company category value.

Business Rules

1. Only company records are allowed to have the category changed.
2. All contact records must have the same category values as their Company has.

Strategy

To crack this nut it took learning these things:

1. Transact-sql looping
2. Transact-sql sophisticated 'select' expression (maybe just sql-based, but I wasn't aware of them)
3. Recursive trigger enabling/disabling. The documentation says that disabled is the default.
   • To enable/disable: If the recursive mode is not off, in a new query window of sql server mgmt studio, execute the command inside the brackets: <sp_dboption '<name of database>', 'recursive triggers', 'true', reconfigure>. That will disallow all recursive triggers so an endless loop won't occur when we operate on the trigger table.
4. The next obstacle is a unique index made up of: client_id, contact_number, type_id, code_id. We cannot violate this index by trying to insert a record that has the same values for those fields as an existing record.
5. Maximizer only performs inserts and deletes on the amgr_user_fields_tbl table. Hence, update triggers don't get called, so we must use a different strategy consisting of a separate insert and delete trigger.
   • First the insert trigger is called. The special 'inserted' table contains the company record that is being inserted or updated. Hence, collect the data from the special 'inserted' table. In this table we will have the changed code_id. Code_id is what differentiates the possible values that the category system UDF can take. Insert a new record with all the same values as contained in the special 'inserted' table for any contact_numbers > 0. Contact number = 0 is the company record. Then we have all the new code_id's now inserted into the table.
   • Second the delete trigger is called and we just delete all the "old" records with the values from the 'deleted' table, except we use contact_number > 0, so we only delete the "old" contact records.

Features

1. Disallows changes to category made on a contact record
2. Stored procedure synchonizes entire database in 5 minutes.

Remaining Issues

1. Error handling code.
2. Testing rigorously to prevent f!ckups!
3. Follow-on: send email notifying of an attempt to change contact category records...add any other needed notifications

Installation

• Create two tables
  • dbo.error_log - table that gets written to when an exception is thrown
  • dbo.debug_log - table that logs debug statements

   create table dbo.error_log {
      [error_number] [int] NULL, 
      [error_msg] [varchar](100) NULL
   }

   create table dbo.debug_log {
      [contact_number] [int] NULL,
      [client_id] [varchar](100) NULL,
      [codeId] [int] NULL,
      [msg] [varchar](100) NULL,
      [last_edit] [datetime] NULL,
      [type_id] [int] NULL
   }

• Install triggers (load and execute)

Testing Strategy

1. Just install triggers and let employees work on some companies normally to see that everything is ok.
2. Then run the database synchronize feature off hours to synch up entire database.

Benefits

1. The aforementioned knowledge can be used to enforce other business rules, that Maximizer does not support.
2. Sql triggers/stored procedures can and are being versioned, making it easy to apply to updated databases.
3. Knowledge of debugging techniques for triggers/stored procedures.