Problem with Trigger in SQL Server 2005

Question

Hi, I am having a problem with a trigger in SQL Server 2005, I have created the trigger and I have tested it inserting rows manually and it works fine that way, however I call a Stored Procedure from a c# web application and the triggers does not get fired, so I took same data inserted from the web application, deleted that row and reinserted it manually and the trigger worked that way again but it does not when called from the stored procedure from the web application.

Any advice?

Thanks in advance.

Albert

Answer 1

You should, in general, avoid using nested triggers, and if you can avoid them alltogether, by all means do so. They are hard to debug, are not "visible" to other developers (which could cause logic problems in the future), and can cause performance issues under load.

See this article: Why use triggers in Microsoft SQL Server?

Avoid using nested triggers By default, if a trigger is changing other tables, the triggers declared for these tables are not fired. The "allow nested triggers" server option sets databases to have the opposite behavior. Triggers are nested when a trigger performs an action that initiates another trigger, which can initiate another trigger and so on. Triggers can be nested up to 32 levels. It is very difficult to follow the logic of nested triggers and they can affect performance.

Answer 2

instead of triggers use the direct insert statement from your web application. that way it would be easier to monitor and debug.