CODEWITHSUNDEEP
sqlsql-servertriggers
Jamie Carruthers
Jamie Carruthers

Reputation: 685

Efficiency of having multiple triggers on 1 table

I am planning on putting 10-15 insert triggers on a single table. Each trigger will do 3~ inserts to another table. Will this have an adverse effect on my database? I'm thinking the speed it takes to execute all the triggers may be a little slow.

The reason for this is a 3rd party peice of software logs data in a database for us. The table scheme is like so:

DateTime EquipA_Speed EquipA_Power EquipB_Speed EquipB_Power EquipC_Speed EquipC_Power

We then want to normalize this data in our own database. Our database has an Equipment table and a SignalType table (signals being power, speed etc).

My plan was have a trigger for each piece of a equipment to make it simpler and more maintainable. When a row is inserted, I need to add a row for each signal for each piece of equipment in a Signal table.

SignalID   Value  EquipmentID   SignalTypeID

Upvotes: 0

Views: 756

Answers (2)

gbn
gbn

Reputation: 432271

You could poll the wide table, or use service broker from the trigger to notify a decoupled process that reads the table.

Then you can have a stored that prepares/shreds the data first in temp tables or table variables first. Then starts a transaction, do your writes, commits.

Upvotes: 2

Burt
Burt

Reputation: 7758

It depends exactly what the trigger does. The trigger won;t be the performance hit, the logic the trigger contains will have a read at the second last paragragh in this article for more details:

http://msdn.microsoft.com/en-gb/magazine/cc164047.aspx

What you could do is set up a test case. Another thing to watch is the size of the log file (.ldf) as it will grow quite large depending on how often an insert is done.

WHy would you want to this anyway?

Upvotes: 0

Related Questions