CODEWITHSUNDEEP
sql
conectionist
conectionist

Reputation: 2914

Is it better to do database operations from an sql script or from application code?

Consider the following abstract situation (just as an example):

I have two tables TableA and TableB. They have unique IDs and possibly other columns (which are irrelevant) The relatioship between them is many to many so I have a third table AssociationTable that is used to store the relationships between them. Basically, AssociationTable will have two columns (ID_A and ID_B - foreign keys).

If I delete a row in AssociationTable and the ID_A that was deleted was the last one, I would also like to delete the entry from TableA that corresponds to that ID.

I could do this:

a) From the application that uses the database

b) by using an SQL trigger

My question, basically, is the following:

Is there any good practice that says "if you can do something from both the application and from SQL, always prefer sql." ?

Or does it depend on the case? If so, what should I take into account?

Upvotes: 0

Views: 122

Answers (2)

RRM
RRM

Reputation: 2669

  1. Performance: The query plan for stored procedures is compiled onn DB Server and subsequent requests can run faster. A stored procedure can execute multiple steps and the intermediate results need not go back to application layer, reducing traffic between an application and the DB server.

  2. Security: Stored procedures are well defined database objects that can be locked down with security measures. Use of typed parameters can help prevent SQL injection attacks.

  3. Code re-use: SQL queries can be written once and re-used across multiple clients without writing the same SQL commands over and over again.

  4. Abstraction: By putting all the SQL code into a stored procedure, the application is completely abstracted from the field names, tables names, etc. So when a SQL query needs to be changed, there is almost zero or NO impact in the application code.

Upvotes: 1

RRM
RRM

Reputation: 2669

There are more benefits of doing it in the database.

  1. Other client application code need not worry about data integrity. The data logic should remain as close to data as possible

  2. It could be faster if managed by DB (trigger invocation).

Upvotes: 1

Related Questions