Should I use a database trigger for the following problem?

Question

Hi,

I've been given the following task:

An external source will write a name, datetime and value to a database table (table A) at random intervals. I need to copy this data to another table (table B) and delete the row in table A. If the name doesn't exist in a lookup table then the row needs to remain in table A until it does and then copied over to table B.

Does the above sound like a situation where a database trigger could be used effectively or would it be better creating an external app (maybe windows service) that checks the db every 5 min and performs the necessary updates?

Update:

This does not necessarily need to be instant thus if I used a windows service or task scheduler it would probably be set at 5 min intervals. It being virtually instant if a trigger is used is just a bonus of using a trigger, not a deciding factor.

My main concern is that is this the correct way of using a trigger? Should triggers be used to copy data or is this bad practise? Also could have problems with using a trigger, for example if a copy fails could it lock the table stopping subsequent rows being copied? If one copy takes too long will it not process the rows that have been inserted while it was bus? If I don't use a trigger is there a better solution e.g. a windows service or console app using windows schedular?

Thanks

Answer 1

This sounds like a very simple operation. If it is, and it doesn't take much time to delete the row from table A and insert it into table B, I would go for the trigger. This has the advantage that data is always up-to-date, as opposed to synchronising regularly.

I'm assuming you have no control over how the data is inserted, or I'd suggest having the external source write to your database through a stored procedure. This can also improve security.

Answer 2

You can do this using SQL Job. Write Stored Procedure to copy data and put in schedule job. This job will run after every 5 minutes.

step_by_step_guide_to_add_a_sql_job_in_sql_server_2005

sql_server_agent_jobs

Answer 3

If this is the only task of its kind and you don't expect more to come, then a trigger is sufficient. It sounds like a simple data manipulation. Better put the code in some stored procedure. A service would be the overkill.

The problem with such tasks is that they tend to grow. I have already seen simple data manipulation logic to grow up to enormous business logic beasts which devours the programmer’s sanity. So if you start with an innocent trigger/stored procedure and just sense a little probability that more will come, then run and make a service out of it!

Answer 4

Write a simple trigger, and in a transaction do the check and the updates you need.

Answer 5

Trigger is the solution. And it also happens to be a part of transaction. So, whole thing happens or fails.

Answer 6

As long as the logic is simple I'd say go ahead and use a trigger. Problems begin to arise when triggers implement complex logic, but if it's just "Verify that data exists in the lookup table, and if it does copy the data from A to B and delete from A".

You might also consider a second trigger on the lookup table so that when a new name is added to the lookup table it could do whatever processing is needed to move rows from A that needed the new name to table B, etc.

Share and enjoy.

Answer 7

Triggers lure you in as a simple fix, but before long, you spend more time debugging triggers than reading StackOverflow. Triggers are evil. Stay away from them.

Answer 8

I agree with Muhammad. This should be done using a database job.

The use of a trigger in this situation seems counter intuitive to me. You are using a trigger not to modify data you are inserting in table A, but to (potentially) do something in table B and prevent the row from being inserted in table A.

Also, in the situation where you have a row inserted into table A for which the key does not exist, this row is left in table A. This means your trigger will have to be table level, not row level.

The main use case for triggers is to modify data on insertion, adding information such as modifcation dates.

You should write a stored procedure and run it every five minutes. This is simpler, and when the logic changes will be much easier to understand.