S3.6 - Table Triggers and Passive Mode

3.6.1      Triggers

You may wish to have a system in place that keeps the keys table in a <dataSource> produced by matchIT SQL kept in sync and up to date with any changes that occur within the tables of data specified in the data source. matchIT SQL offers a system to take care of this in the form of triggers. In the ‘scripts’ folder, you will see two scripts called ‘CreateTriggers.sql’ and ‘DropTriggers.sql’, the names of which are self-explanatory. If you open up the first script, ‘CreateTriggers.sql’, the lines which you will need to amend are the following.

USE matchIT_SQL_demo

ALTER DATABASE matchIT_SQL_demo

Simply amend these lines to point to the database that contains the tables you are creating the triggers on.

'C:\Program Files\matchIT SQL\bin\StoredProcedures.dll'

Amend this file path if necessary to point to the assembly you wish to create the stored procedures from.

'C:\matchIT SQL\demo\Config.xml'

Amend this file path to point to the configuration file that contains the data source defining the tables you wish to create the triggers on.

Note – Once created, the triggers rely on the Configuration file being unchanged and staying in the same location as when created. Triggers are only really recommended for long term setups that are not going to change. The triggers created on the tables in data source specified when calling msp_CreateTableTriggers also rely on the fact that the database in question has the msp_GenerateSingleKeys stored procedure on it too (which gets created along with the assembly in CreateTriggers.sql). If this procedure is not available in the database, the triggers will simply do nothing. Triggers should be deployed with care and testing to make sure no errors occur that could prevent database updates, inserts and deletions occurring in the tables that the triggers exist on.

As an overview then, and as can be seen in the following line

EXEC msp_CreateTableTriggers @config='C:\matchIT SQL\demo\Config.xml', @dataSourceID='1'

Triggers are created on a particular database using the specified configuration file on the tables defined in the specified data source. Once created, the triggers, when fired, rely on the Configuration File they were created with being in the location that was specified on creation, and for accurate results, for it to be unchanged.

To remove triggers, and all associated procedures and the assembly, make the same style modifications as per the create script above – The configuration file and data source used to remove the triggers will need to be the same as when they were created.

Known Issue:

Triggers must be hosted in the database containing the data:

When using the matchIT SQL triggers (via the CreateTableTriggers and DeleteTableTriggers stored procedures) these must be hosted on the database referenced by the connection string in the configuration file. If the triggers and the data are hosted on different databases, then the triggers won't execute when insertions, updates, and deletions are made.

3.6.2      Running in Passive Mode

It may be necessary, depending on the scenario, to run some match processing in the background that is not required in real time. An example of this might be an e-commerce website that has members joining every week – where it may be necessary to run a weekly process to gather all the new joiners and then search for matches in the database. In this situation, the best thing to do would be to gather a collection of records for which you wish to wish to check against the database for duplicates, and run the msp_SingleRecordMatch2 stored procedure (described here) for each one and log the matches for each record as suits the situation. See the description of msp_SingleRecordMatch2 for information on what it returns and the parameters it accepts.

As a note, the TVF Functions for single record matching and others are not part of the default functionality and are licensed separately.