Alternative to using triggers

[divinyl]

Hi all

I work as a production dba and our development team are trying to push a project which involves using triggers. The aim is to transfer information between to databases (on two differents servers) because currently users have to type in the same info into the two different systems.
The triggers will be defined on a couple of tables, checking for inserts, updates, deletes, and then insert this into staging tables within teh same database. However the trigger does more complex processing than just inserting the same records from the production table into the staging table. Because the schema between the source database and destination database is different, the trigger needs to do some manipulation before it updates the staging tables. It basically does massive selects from a number of different tables to get the desired column list & then puts that into the staging tables.
We have basically asked them to reimplement this solution using other methods (such as timestamping the necessary tables and then putting the trigger login into a stored proc and scheduling it to run through a job).

However, we've found out the triggers make use of the 'deleted' and 'inserted' special trigger tables to compare new data to old data - i.e. not all inserts/updates/deletes need to be pushed to the staging tables - it depends on certain criteria based on this comparison of old and new data.....that throws a spanner in the works. What alternatives could provide this functionality, without just making the whole process a a headache to maintain - which is why we recommended not using triggers in the first place!!

Sorry for the long post - needed to explain the issue properly. Hopefully some of you will be able to provide some feedback - teh sooner the better as I have a meeting with the developers later today and would like to offer some alternatives.

Thanks!
Div

 

[RiverGuy]

Without knowing anymore specifics, my recommendation would be to use an ETL tool such as SSIS to move the data from the production server to the other server. Have them develop all of the data cleansing and transformations in the SSIS packages. Having the records timestamped in your production system should be sufficient enough for them to grab any changed records to load through the ETL process.

 

[divinyl]

Thanks for the quick response. SSIS is a good solution for this project as a whole, but what about the issue with them needing to keep track of the old data? When a customer does an update to a table it overwrites the data - but tehy need to do comparisons between the old values and the new values before transferring the data across? They would have to keep a snapshot of the table wouldn't they? WHich is a bit of a headache in itself...

 

[RiverGuy]

Although this doesn't sound like a data warehousing project, there are data warehousing features in SSIS that you can take advantage of to compare old values with current values such as the Slowly Changing Dimension Wizard. A repository of the history of your data isn't a bad thing IMO.

 

[SQLSister]

You could simply have the trigger throw the old values to an audit table and then do your other processing in a job that runs on a regular interval that compares the two tables. Just make sure the trigger can handle multiple records being inserted or deleted or updated and that it does not do so using a cursor.

"NOTHING is more important in a database than integrity." ESquared

 

[mrdenny]

Sounds to me like triggers should be setup to look at inserted and deleted to figure out which rows to need to moved, then insert those records into staging tables in the same format that the source records are in.

Then use SSIS to take those records, join them with the other tables as needed to get the transformation into the destination format done, then insert the records into the destination table.

The trigger logic could be kept fairly simple, with little to no load on the other tables in the system, with SSIS doing the heavy lifting a few times a day (or however often is required).

Don't apologize for long posts. With questions like these more information is better than not enough (and I don't think you provided to much info).

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog