I've been wondering about the use of SQL triggers to capture table data and store it in a log before it's overwritten.

One thread I found which mentioned it (very) briefly but was an interesting read is this one - http://www.dotnetnuke.com/Community/Forums/tabid/795/forumid/118/threadid/250006/scope/posts/Default.aspx

Look at the DNN database, there are no triggers at all.  From over 2 years of adding various 3rd party modules I was a little surprised though I'm not a developer so it does raised some questions in my mind.

Are there issues with using triggers that I mightn't be aware of?

I would have thought log files, deleted items etc might have been handled via triggers but it looks like this isn't the case. Are there advantages in using queries and stored procedures instead of this?

Interesting thread.

there are a couple of good reasons for not using triggers

1. not all databases support triggers, so it would make it harder to create non-sql server/oracle versions of the core/modules. It's better to keep this logic in the businss layer.

2. if a trigger fails it can cause an action on a table to fail i.e. a trigger to log changes to a table may cause the change to fail.

However, for me the biggest problem with triggers is 3rd party developers using them and causing core upgrades to fail (i see this on modules that try to do cross-portal logins via triggers on the users or portals tables). If the core attempts to alter a table (e.g. to add columns) the existance of a trigger can cause that to fail causing the whole upgrade to fail. Whilst it's fine to add triggers to any tables your own module creates , if you add a trigger to a core table you're asking for trouble.

Cathal