Would you avoid using database table triggers?
Posted on 2007-11-19
In order to improve performance of my web application, I took some bulky database processing and moved it into database level procedures. I also added an insert/update trigger on a few tables to automatically populate child tables when a record was added or modified. Everything worked great and much much faster.
Now, however, I am in deadlock hell. Tables are deadlocking right and left. I don't understand why tables deadlock on insert statements (of tables with no triggers).
I'm investigating several paths, including having just upgraded to Coldfusion 8. My question for this post is, are using table insert/update triggers and procedures more prone to deadlock? Should I rip them out because they are tripping over each other. The procedures have many insert and update statements have between two and six insert/update statements in them.