Django code or MySQL triggers

Question

Hi. I'm making a web service with Django that uses MySQL database. Clients interface with our database through URLs, handled by Django. Right now I'm trying to create a behavior that automatically does some checking/logging whenever a certain table is modified, which naturally means MySQL triggers. However I can also do this in Django, in the request handler that does the table modification. I don't think Django has trigger support yet, so I'm not sure which is better, doing through Django code or MySQL trigger.

Anybody with knowledge on the performance of these options care to shed some light? Thanks in advance!

Answer 1

What you're describing sounds like "change data capture" to me.

I think the trade-offs might go like this:

1. Django pros: Middle tier code can be shared by multiple apps; portable if database changes
2. Django cons: Logically not part of the business transaction
3. MySQL pros: Natural to do it in a database
4. MySQL cons: Triggers are very database-specific; if you change vendors you have to rewrite

This might be helpful.

Answer 2

There are a lot of ways to solve the problem you've described:

• Application Logic
  • View-specific logic -- If the behavior is specific to a single view, then put the changes in the view.
  • Model-specific logic -- If the behavior is specific to a single model, then override the save() method for the model.
• Middleware Logic -- If the behavior relates to multiple models OR needs to wrapped around an existing application, you can use Django's pre-save/post-save signals to add additional behaviors without changing the application itself.
• Database Stored Procedures -- Normally a possibility, but Django's ORM doesn't use them. Not portable across databases.
• Database Triggers -- Not portable from one database to another (or even one version of a database to the next), but allow you to control shared behavior across multiple (possibly non-Django) applications.

Personally, I prefer using either overriding the save() method, or using a Django signal. Using view-specific logic can catch you out on large applications with multiple views of the same model(s).