SQL Express, Connections and triggers

[venerable bede]

Hi All,

Can someone tell me if when a trigger is fired does it consume a connection and how does this effect performance ?


Thanks In Advance

[techgnome]

no... it runs completely on the server, within the current connection. As for performance... depends on what the trigger does.

-tg

[venerable bede]

So what you are saying is that if sql only supports 1 connection and I update a table which in turn fires off 3 triggers the triggers will not require that 1 connection but will do all the work internally without any concept of the connection object.

[techgnome]

not completely... it happens within that context of the connection - transactions will be respected, as will user permissions, so if in the trigger it tries to write to a table the user doesn't have access to, it will fail (it should at least). It doesn't need additional connections since the action is being performed on the server.... it's the same if you called a stored procedure, which then called another, which in turn called another and so on.... it's all within that same single connection.

-tg

[jmcilhinney]

Think about what a connection is for. It's just a pipe for transferring data between the client and the server. The trigger isn't going to transfer any data between the client and the server so what use does it have for a connection at all? When you open a connection a session is created and any operations that you initiate over that connection occur within that session, but they don't have anything to do with the connection.

Consider this. You ring up the takeaway store and order some food. The person you spoke to tells someone else to make part of it. Do you need to make a separate phone call to tell that person to make their part?