trigger calls stored procedure- sometimes?

Hot Topic

Enterprise Manager: Auto Parenthesis
URGENT: Insert and Update in a transaction is getting blocked
Parent Child Dimension Question
Populate LOOKUP query from a variable
Need help regarding Replication between SQL Server 2005 and SQL Server Mobie Edition!
How can i insert a default row if it doenst exists already?
AS2005 Cube Processed "Successfully", But Data Not Refreshed
Streams in SqlFunction
how do I use the crosstab query in sql server mobile edition
SSIS - how to set ServerName and DatabaseName at run time inside FOREACH LOOP?

SQL Server

error creating named calculation
Trying to do too much in a view... I think?
Slower Execution Of SISS Packages
Replication Deadlocks
INSERT fails - problem with SQL command or SQL server?
Trusted SQL Connections....
Notify Operator Task -- no database mail profile option
VB 2005 & Small Business Server & SQL Server
@@CPU_BUSY stops incrementing and generates "Arithmetic overflow occurred"
How to create a new named instance

trigger calls stored procedure- sometimes?

Hi ,
    I have a trigger that propogates data to other databases by calling an sproc within the body of the trigger. However, if the trigger fires more than once in quick succession (ie a few rows are quickly added to the table) the support sproc is NOT called the equivalent number of times ,frequently only once. Why is this or is the theory totaly wrong

Dave 

Answer this question

trigger calls stored procedure- sometimes?

  • microvibratom

    IdeaNope. it was me all along. I never new that the internal deleted and inserted tables held multiple rows, i wrongly (very it would seem) assumed that as a trigger fired once per insert delete or whatever, the inserted and deleted tables could only contain at most one row each. I now have a cursor inside my trigger that loops over the internal deleted and inserted tables before sending the approriate parameters to the extarnal sproc (which , just to add to the performance woes contains another cursor that loops over all production databases using some dynamic sql to see if it needs to be updated!!) I'll ponder a quicker way now that I've got it to do exactly what i want.

  • Heinz12

    It is not possible and shouldn't be happening. It is highly unlikely that such a major bug is there. There might be something wrong in your trigger logic or SP code. You should also consider doing these type of operations outside of the trigger. It is very expensive to make distributed calls from within triggers and you are also doing this within an implicit transaction (due to trigger). Use a batch process instead that can identify the added/modified rows and propogate them to other databases. Lastly, why are you not using replication to do this It can do the job more efficiently and cleaner. Replication also supports hetergeneous data sources and is much improved in SQL Server 2005.

  • trigger calls stored procedure- sometimes?
Answer this question