what’s the best way to do this operation

Question

hi ,,,

I have a question about what’s the best way to do this operation: I have a database let’s assume that it has 4 tables(the number of table could go up much higher), when I want to do any CRUD(Insert,update,delete) operations for table1 , table2 and table3 I have to do the same operations to table4,now table4 is very critical so I have to make sure all operations are accurate and fast , eventually this table will be used to get system reports so I need it to be fast Now which of the following two ways will be better :

-The first method happens in the database so I create a stored procedure or trigger and when any operation is performed in any table (table1,table2,table3),this activates the trigger that performes the same operation In table4

-The second method happens in program code(asp.net and c#), when any operation is performed for any table (table1,table2,table3) I do the same operation to table4 using class or any code to do this.

And what are the things that we need keep in mind when we choose either way such as security , Database size, application Architecture,and…..

thanks

Answer 1

If you mean that table4 is only for read only used for generating reports, it should be a view not a table. But if it should be a table, doing it at a database level using triggers is good. As it is faster, secure and in future, if any other applications update the other tables the data is automatically updated in table4 and application logic need not be re implemented.

Answer 2

I like Teja's idea of using a view for table4 if that works (i.e., that table does not contain any data not in table1, table2 or table3).

When considering using stored procedures, think of what would happen if anything changed about the schema of any of the tables. What if you had to revisit every single stored procedure and alter it? If there are a lot, that could take a lot of effort, and introduce opportunities for creating bugs.

Triggers are easier to alter than stored procedures. But they do carry a performance cost. They can really slow your app down if a lot of tables have a trigger for every write operation.

Answer 3

thanks all for your prompt response,,,

i understand that using trigger is a good option to some point but i was thinking ,what if :

I creat a function to (insert/update/delete) in tableC and this function could be called from any stored procedure that would (insert/update/delete) tableA and tableB. This would allow me to manage one function if a change is needed instead of modifying the code in every stored procedure. In addition, I can use a transaction in every stored procedure to keep data in all tables consistent .