SQL Server Performance Forum – Threads Archive

Can anybody tell me why we shall use less trigger

first, sorry for my poor English. [:I] Others often tell me that shall use less trigger because trigger may cause performance problem. Anyone can tell me why? I want to know how trigger make the performance problem. thx.

I don’t know why ther said that.
Of course if try to use trigger like store procedures, you will have problems. Triggers should used to do some little work when you access one table. Could you show some example of yours triggers?
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

In my opinion using triggers is fine as long as you are aware how often they will run, and what the cost of running the trigger is. For example, having a complex trigger on a table that has 50 rows inserted every second may hurt performance, but sometimes there may not be a way around having to do this! However, the same trigger on a table that only has 5 rows inserted an hour might not be a problem Ben ‘I reject your reality and substitute my own’ – Adam Savage

quote:Originally posted by benwilson In my opinion using triggers is fine as long as you are aware how often they will run, and what the cost of running the trigger is. For example, having a complex trigger on a table that has 50 rows inserted every second may hurt performance, but sometimes there may not be a way around having to do this! However, the same trigger on a table that only has 5 rows inserted an hour might not be a problem Ben ‘I reject your reality and substitute my own’ – Adam Savage

yes, you are right.
I just want know, for example, there is a task to inserted 50 rows every second, I use
1. 50 individual SQL statement
2. A transaction include 50 insert statement
3. A stored procedure which responsible for inserting 50 rows
4. A trigger on a table that has 50 rows inserted every second why trigger is badder than 1 or 2 or 3.
maybe trigger have exact frequency, like N rows/sec. but I think transaction or sp has the same frequency. So, why trigger is badder? Wish my statement is clear,lol

HI,
in general as we know,we used to develop trigger to implement business rules that cannot possible to set with ref integrity, As we know trigger is used when the Constraint are not capable to achieve our business principle, for example when you have to force Integrity based action e.g. rollback transaction, wants to set the trace on certain action, if you wants to validate a column value against a column in another table, if your application requires customized message and error handling for more information on trigger read following article :
http://www.sql-server-performance.com/nn_triggers.asp
http://www.sql-server-performance.com/trigger_tuning.asp
http://www.sql-server-performance.com/developers_tuning_tutorial.asp and its depend how u insert the records in a bulk or individual statements , Store procedure is recomended to insert.
basically trigger is fire before your insert / update / delete statement fire
HTH
Regards. hsGoswami
[email protected]
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemant Goswami

might have to wait for the real experts to voice their opinions. Personally, i can not see why a trigger would be worse than any of the other alternatives. EDIT: ghemant- you must have made your post while i was typing mine so i was not aware there would be a post before mine- i am not trying to imply you are not a real expert, just that i am not!!! Ben ‘I reject your reality and substitute my own’ – Adam Savage

Hi, say for example i have table , tableA which is very often used (their are 1000 rows are insertion / updation ) and i have created trigger on this tableA for insert that check the integrity for insertion of record from another table ,tableD and what happens in this case my 1000 rows are waiting to be insert (it waits untill my tableD veryfies it that it should be veryfied okay (business rull is / are set )) and after validation is done then only it actually inserted in my tableA … this is the scenario why we should use less trigger in my opinion use trigger only when contraints fails to set business rull / integrity . i am also waiting for expert to verify my answer and see if i could make understand / answer proper. Ben : as we are here to share knowledge and to get more from here and we are just a trying to make it more precisely explaining our opinion and not advice , advice are always from our real experts [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] m also waiting for an opinion that wether my explaination/answer are enough or not ,because i also do beleive that i am not expert [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />][:I]. HTH Regards. hsGoswami [email protected] "Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemant Goswami

I rewrite my statement. We can divide a task of trigger into two parts: rule check and data operation, right?
So when someone said : yep, sp is recommended.
I shall say: maybe, but you shall notice that the sp must include the rule check part. otherwise it’s unfair. ok, then I want to know the difference between trigger and sp which has the rule check part.
Now I can only say that the difference is that sp is executed by hand, and the trigger is executed by system.
So I doubt "sp is recommended, use less trigger".
of course, this time we have a hypothesis that the business rule must be to do with. At last, I think maybe, just maybe inserted and deleted are the key. these two virtual logic table maybe cause some performance problem. But I really can’t confirm it. ^_^,i am also waiting for expert to verify my answer imperiously and see if i could make understand / answer proper.

Performance with the inserted and deleted tables depends totally on how you handle them. The most common problem is when developers have a background in programming, where procedures are handled step-by-step, and they write triggers using a cursor – in short, they need to learn how to write "set-based" code. Then again, there are situations where you cannot avoid doing stuff step-by-step. You should avoid calling stored procedures from a trigger because … well, the insert/update/delete action will not finish before the sp does, so you could run into ugly locking issues, bad response times …………. And of course another nice thing about triggers is "set and forget" – they just fire when called upon, you don’t need a provision in the client app like with a stored procedure.

I think I’ve lost you somewhere in this thread. Can you actually post what now your question is? If you have problems with your code, please post it. Otherwise it will be very hard to suggest something useful. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

Frank, Question was : Why triggers are not used much often ?! or we can say impact of Trigger while we have multiple records / bulk records are inserted / edited / deletion in seconds(e.g 1000 records per second) then what you suggests ! say for example i have table , tableA which is very often used (their are 1000 rows are insertion / updation ) and i have created trigger on this tableA for insert that check the integrity for insertion of record from another table ,tableD and what happens in this case my 1000 rows are waiting to be insert (it waits untill my tableD veryfies it that it should be veryfied okay (business rull is / are set )) and after validation is done then only it actually inserted in my tableA … this is the scenario why we should use less trigger in my opinion use trigger only when contraints fails to set business rull / integrity . [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Regards hsGoswami [email protected] "Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemant Goswami

I know, but then the original questioner stated <blockquote id="quote">quote:<hr height="1" noshade id="quote"> … ok, then I want to know the difference between trigger and sp which has the rule check part. … <hr height="1" noshade id="quote"></blockquote id="quote"> Weired thread. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Ich unterstÃ¼tze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

I agree it’s all a bit vague. The underlying question is something like: "Should we implement business rules using sp or using triggers". I responded to the bit where tamsun mentioned performance related to the inserted and deleted tables, so I assumed he was thinking about calling sp’s from within triggers.

Hm, at first I was about to answer to the question where triggers can affect performance. I guess a very good example here is to have a trigger sending some mail alerts. [<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />] Anyway, I really would appreciate a bit more specific question(s) in order for all to provide quick and good suggestions. – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Ich unterstÃ¼tze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

I agree with Frank as it always "it depends." Writing a bunch of mindless stored procedures to perform every database operation you think you may need is definitely not. The problem with using stored procedures in the way you mention is that you don’t have the fine granularity that you get with using views and granting select/update/delete to tables or columns as/if needed. With most implementation of SP once you get to the SP you can enter any information as the parameter Addition:
The execution plan for stored procedures and triggers is executed separately from the execution plan for the batch calling the stored procedure or firing the trigger. This allows for greater reuse of the stored procedure and trigger execution plans. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Again agreed.[<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />]

Hi,
thats fine sir , we agree but still the question of orginator’s is their !

quote:
tamsun :
I just want know, for example, there is a task to inserted 50 rows every second, I use
1. 50 individual SQL statement
2. A transaction include 50 insert statement
3. A stored procedure which responsible for inserting 50 rows
4. A trigger on a table that has 50 rows inserted every second why trigger is badder than 1 or 2 or 3.
maybe trigger have exact frequency, like N rows/sec. but I think transaction or sp has the same frequency. So, why trigger is badder?

and as my opinion we should implement triggers in order to implement some business rule that can not be enforced using constraints or specific action (mail alerts) other wise we should let it happen with sp / stements because as i said earlier it will for wait to another resource to use :

quote:say for example i have table , tableA which is very often used (their are 1000 rows are insertion / updation ) and i have created trigger on this tableA for insert that check the integrity for insertion of record from another table ,tableD and what happens in this case my 1000 rows are waiting to be insert (it waits untill my tableD veryfies it that it should be veryfied okay (business rull is / are set )) and after validation is done then only it actually inserted in my tableA …
this is the scenario why we should use less trigger
in my opinion use trigger only when contraints fails to set business rull / integrity .

what is your suggetion !? [:I]
Regards hsGoswami
[email protected]
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemant Goswami

Data validation should first happen at the middle-tier, then in the sp. A trigger is really not the best place for this. A trigger’s code should be optimized to execute as fast as possible so that it won’t block any operation for long. Usually this means that trigger logic is rather simple, while the more complex things happen at other parts of the calling app and the database. But then again this is a broad answer to a broad question. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

It is true that Triggers are helpful to enfore business rules, but make sure they should not block other process while working out. Again "it depends" how you want to use them and not that you should not them at all. I believe we need to wait for Tamsun to comeback about specific reason behind the question.
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

But think of a two-tier system, like a client app using "linked tables" that are directly accessible to users for data entry, like when using Microsoft Access. In that scenario a trigger is the only mechanism that is guaranteed to intervene. So if you need to enforce business rules in such an environment, you can’t avoid putting it in a trigger.

What about Class Modules or some ActiveX DLL’s?
Just curious, wouldn’t you use them in such a scenario? —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

Frank, If we ever get serious about programming here – we might, someday …[<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] Problem is that our clients in general can’t afford to spend lots of money on modifications to our base systems involving custom DLLs or even network services. Also, they tend to find out after installation that their requirements were not quite right, and need some adjusting – such issues are easier to solve in a ‘stupid’ Access system with bound forms, and a couple of triggers on the back-end. By the way, we do use classes, but we have a history of supplying "open" systems that customers can customize, so we have to leave things open – with a stern warning to the customer that we are not responsible for anything going wrong with the system or the data once they start customizing themselves.

hi,
if it have some validation using some module /activex controls then it uses some frontend tool to validate and what if someone who know the basic T-SQL DML and he/she does some modification in data !? hsGoswami
[email protected]
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemant Goswami

I know what you mean. When I was programming for a living, the company I worked for just did the same. But with Access 97 as front-, and back-end database [xx(]
They switched the backend first to Oracle and nowadays to SQL Server 2000 after I left. I know these situations when you as the developer know, there is a "better" solution, but simply don’t have the time to implement it because of those d*mned deadlines. And once this kludge goes into production, there is no time to improve it because one is concerned with new requirements and projects. Frankly, this was a major reason why I decide to move away from professional development. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

Hemant, If that is your "disaster scenario", then the solution is a trigger, because this will respond to all insert/update/delete events to which it is linked by definition, regardless of the tools being used for manipulating the data.

Hi Adriaan,

quote: Hemant, If that is your "disaster scenario", then the solution is a trigger, because this will respond to all insert/update/delete events to which it is linked by definition, regardless of the tools being used for manipulating the data.

this not my "disaster scenario" but just a opinion that we dont impose / rely /enforce business rule using front end, and if this could happend , you also agreed on enforcing business rules / validations using trigger but will it impact on performance if the enforced table is havily used !?
i think yes it does.. what is your suggestion !?
hsGoswami
[email protected]
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemant Goswami

Hemant, As long as the trigger is designed with performance in mind … Optimize by focusing the queries in the trigger on the "inserted" and "deleted" tables. Use EXISTS whenever possible. Don’t use cursors unless there really is no other way. Avoid adding or updating data to columns within INSERT or UPDATE triggers, as they will fire any UPDATE triggers all over again.

Hi Adriaan, now i think we have to wait for the originator because what he wants to ask that <blockquote id="quote">quote:<hr height="1" noshade id="quote"> why we shall use less trigger ?! <hr height="1" noshade id="quote"></blockquote id="quote"> and as far as impact on table /performance is concern i try to convince orginator that it does impact on performance if table is havily used (1000/10000 records / per second) and on that table we have enforced trigger to validate / business rule to perform validation before insertion / updatation then it should wait untill validation is over and based on validation result it should commit or rollback transaction… right ! and if the T-SQL is not optimized proper / table is thus havily used it does impact on the performance ! this is my opinion. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Regards hsGoswami [email protected] "Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemant Goswami

Regardless of middle tier validation, business rules, or allowing random inserts or updates — which are all valid trigger considerations, the inserted and deleted tables referenced by a trigger is actually the log file. This means performance is great for small numbers of rows but kills performance on large numbers of rows. My personal biggest reason is that triggers are hard to debug. It is easy to forget a trigger is being fired.

]]>