SQL/VB.NET-based Point of Sale Database Replication

Question

I work for a company that recently switched to a new POS system that is written with VB.NET and uses SQL Server 2008 for its data storage. When we first implemented their system 5 months ago, it amazed me how much more power this new system has compared to the POS we used before.

However, there are small changes that they need to make.  It would probably take me 3 days to tweak the code to do what I want, but the program isn't mine. We've been talking to the developers, and everything we request goes straight to the back burner. So, I've given up waiting on them to implement feaures and I'm going to write my own software to address the missing functions without going so far that I create instabilities in their software. There are going to be two programs I will develop. One will be to work around the basic functions missing from their program that the developers may end up adding later (ending the need for the workaround program). The other is a totally separate proprietary program that addresses needs specific to our business. These features may never be added by the developer, but we have to have them, so we will write it ourselves.

I've studied how their software uses the database, and I've tested all of the different scenarios where I will need to perform updates on their tables to make changes to the transactions. Everything for program 1 is good to go without errors, so now I just need to write the program to automate the SQL executions. On to program 2.

Unfortunately, the only thing I can modify in their database without issues are the rows. Any new tables that I add or columns that I add to existing tables cause their database maintenance program to fail due to an unrecognized schema. The table that I need is the "Tickets" table. When a new transaction is created in the POS, it does a SQL INSERT to add the new transaction. I need this INSERT to trigger a replication from Table A to Table B so that my database will have the new ticket as well. I also need it to trigger an update to my database anytime the POS does an UPDATE to a row. The replication only needs to be one way from them to me since I can perform my own INSERTs as needed.

I read that this can be accomplished with triggers, but I also read that it is bad to use triggers in this type of circumstance (which makes little sense to me because a trigger would do exactly what I need). The replication must be real time. So my question is, would a trigger be appropriate in this scenario? Or is there a better way to handle this?

Answer 1

I'm going to move this thread to the T-SQL forum - they're going to be better able to address your concerns...

---

Talk to me now on

Answer 2

Efficiently written trigger can be an OK solution for the above scenario. As long as it doesn't slow down the system and causes blocking, I think it's OK.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 3

Thanks Naomi.

Now when my program makes inserts and updates to their ticket table, is there a way that I can prevent it from automatically triggering a replication back to my table?

It would be great if their DB maintenance wouldn't fail when I add columns to their table. All of this work is to add 6 columns of bit data. Everything else will work with their tables without a problem. It feels like such a drain.

Answer 4

I think apart from opting for a trigger, you can update their INSERT & UPDATE Stored Procedures and append appropriate INSERT & UPDATE to your table as well. So, after every INSERT to their table, the INSERT for you new table will also fire and same with UPDATE. However, make sure this is done within a single TRANSACTION.

This way without any changes to DDL, you can get you task done. Hope, this helps !

---

-Vinay Pugalia
If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
Web : Inkey Solutions
Blog : My Blog
Email : Vinay Pugalia

Answer 5

That was the first place I went to, but all of the procedures are protected and I can only see the parameters. It's a live database so I have to be careful, but are there ways of unlocking the procedures without damaging the database so I can modify them?

Answer 6

If that is the case, then you can try creating one new SP, then first give a call to the existing one and then write the new TSQL following that call.

---

-Vinay Pugalia
If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
Web : Inkey Solutions
Blog : My Blog
Email : Vinay Pugalia

Answer 7

I actually won't have to do any of this.

There are 17,000+ tickets in the database, so I wanted to avoid polling because I was worried about performance. I wrote a left join query that will give me all of their tickets that don't exist in mine. To my surprise, it takes less than one second to complete. Even when it does have a new ticket, it still takes less than a second. It doesn't affect the performance of the POS either as far as I can tell. I think this will be the easiest, most unintrusive way to grab the tickets.

Now don't go too far because that was an easy one to solve. The real fun won't be for another two or three weeks. Have you ever heard of Chek Chart Motor Information Systems?

Answer 8

I read that this can be accomplished with triggers, but I also read that it is bad to use triggers in this type of circumstance (which makes little sense to me because a trigger would do exactly what I need). The replication must be real time. So my question is, would a trigger be appropriate in this scenario? Or is there a better way to handle this?

Trigger is the last choice. If nothing else will do, use trigger.

Trigger misuse article: http://www.sqlusa.com/bestpractices/trigger-as-fix-it-all/

UPDATE trigger example: http://www.sqlusa.com/bestpractices2005/timestamptrigger/

INSERT, UPDATE trigger example: http://www.sqlusa.com/bestpractices/triggerformissingdata/

---

Kalman Toth SQL SERVER & BI TRAINING