Completed(?) and urgent Triggers and Transactions question

I use the VB.NET transaction to update an sql server 2000 database. I call a number of stored procedures within this transaction.

The stored procedures will update tables. These tables use triggers..

My question is, when does the trigger get called? Is it after the each stored procedure, or is it after the whole transaction?

Jag

the trigger will execute when

- new records is being inserted into the table

- records get update

- records get deleted

from the table

|||

In my opinion triggers fire as soon as the database updated, and transaction update your table as soon as a query fires. Having triggers do too much is a typical mistake. Triggers should be
left to handle only simple tasks.

cheers

|||

How would you define too much?

At the moment, one of my trigger calls a stored procedure that is doing about 4 selects on a table (one with an MAX()), and maybe a 2 updates.

The stored procedure should be optimised, so it should case too much problems later (?)

EDIT: I am expecting the number of selects to increase to about 10, and the number of updates to 3. The selects will be small selects that mostly pull out ids.

Jag

|||

Hi,

The trigger will be fired immediately after the the update was executed. And the transaction will continue after trigger gets executed.

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!

|||

What I was afraid of was that the triggers would not get fired during the transaction, and would be fired after the commit. But after a little testing, I found this was not true.

I am still trying to find out how much I can put into a trigger before a performance becomes an issue. I am not expecting heavy usage - I'm not even expecting moderate usage on the site. Maybe about 10 inputs/edits a day, that's it.

I have written a stored procedure and the trigger calls this if needed. The stored procedure will have upto 20 select statements to pull out values from the database, and then a couple of updates. Is this too much? I don't see it being too much as I am not expecting too much input to be going on.

Jag

|||

Just another quick question.

Same example - there is a transaction with a number of updates/inserts. The tables these apply to have triggers on then. The triggers are fired as soon as the update/insert happens, and not at the end of the transaction.

My question is, if the transaction fails and the changes are reversed, will the changes made by the triggers also be reversed. Common sense says that it should happen, but I need to ensure that it does.

Thanks in advance

Jag