Do triggers decreases the performance? Inserted and deleted tables?

Question

Suppose i am having stored procedures which performs Insert/update/delete operations on table.

Depending upon some criteria i want to perform some operations.

Should i create trigger or do the operation in stored procedure itself.

Does using the triggers decreases the performance?

Does these two tables viz Inserted and deleted exists(persistent) or are created dynamically?

If they are created dynamically does it have performance issue.

If they are persistent tables then where are they?

Also if they exixts then can i access Inserted and Deleted tables in stored procedures?

Answer 1

Performance on what? the trigger will perform an update on the DB after the event so the user of your system won't even know it's going on. It happens in the background.

Your question is phrased in a manner quite difficult to understand.

Answer 2

Yes, a table with a trigger will not perform as well as it would without it. Logic dictates that doing something is more expensive than doing nothing.

I think your question would be more meaningful if you asked in terms of whether it is more performant than some other approach that you haven't specified.

Ultimately, I'd select the tool that is most appropriate for the job and only worry about performance if there is a problem, not before you have even implemented a solution.

Inserted and deleted tables are available within the trigger, so calling them from stored procedures is a no-go.

Answer 3

It decreases performance on the query by definition: the query is then doing something it otherwise wasn't going to do.

The other way to look at it is this: if you were going to manually be doing whatever the trigger is doing anyway then they increase performance by saving a round trip.

Take it a step further: that advantage disappears if you use a stored procedure and you're running within one server roundtrip anyway.

So it depends on how you look at it.

Answer 4

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

http://msdn.microsoft.com/en-us/library/ms191300.aspx

Answer 5

Will it be less performant than doing the same thing in a stored proc. Probably not but with all performance questions the only way to really know is to test both approaches with a realistic data set (if you have a 2,000,000 record table don't test with a table with 100 records!)

That said, the choice between a trigger and another method depends entirely on the need for the action in question to happen no matter how the data is updated, deleted, or inserted. If this is a business rule that must always happen no matter what, a trigger is the best place for it or you will eventually have data integrity problems. Data in databases is frequently changed from sources other than the GUI.

When writing a trigger though there are several things you should be aware of. First, the trigger fires once for each batch, so whether you inserted one record or 100,000 records the trigger only fires once. You cannot assume ever that only one record will be affected. Nor can you assume that it will always only be a small record set. This is why it is critical to write all triggers as if you are going to insert, update or delete a million rows. That means set-based logic and no cursors or while loops if at all possible. Do not take a stored proc written to handle one record and call it in a cursor in a trigger.

Also do not send emails from a cursor, you do not want to stop all inserts, updates, or deletes if the email server is down.