| Author |
Topic |
|
global_erp_solution
Starting Member
28 Posts |
 Posted - 2012-10-16 : 02:36:25
|
| is it possible for triggers in 2008 to be activated by trigger also? usually it's fired by DML event. thanks |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2012-10-16 : 04:47:19
|
| What do you mean by 1 trigger firing another? If you have a trigger on a table that inserts/updates/deleted records into/from another table/view that has a trigger on it, you will fire that trigger.I am not sure if this is what you are asking though. Look up Nested Triggers and do a LOT of reading about them before attempting to use though. Read all the pros and definitely the cons. |
 |
|
|
global_erp_solution
Starting Member
28 Posts |
Posted - 2012-10-16 : 05:34:58
|
| I meant something likecreate trigger trigger_first on trigger_secondso you see, trigger activated by trigger. is this possible in SQL Server 2008? |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-10-16 : 06:49:56
|
| It does not work quite like what you described. A trigger can cause another trigger (or even itself to be fired), but DML triggers are ALWAYS fired in response to an update, insert or delete statement. So if there are updates, deletes, or inserts in your trigger code, and if the table(s) that are affected have triggers on them those trigger codes will then be executed.Whether a trigger can cause another trigger to be executed(so-called nested triggers) or cause itself to be executed (recursive trigger) are determined by database and server level settings of nested triggers and recursive triggers. See here, under the two sections "Recursive Triggers" and "Nested Triggers": http://msdn.microsoft.com/en-us/library/ms189799.aspx |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-10-16 : 09:27:37
|
| I dont like the idea of putting nested triggers in place as without proper documentation its makes it quite hard to maintain the code. Most occasions where we had to put some kind of logic which requires nesting DML operations we usually do it by wrapping them inside procedure with or without a transaction. This has the advantage of keeping all logic related to a functionality in same place and easier to maintain. We've inherited systems with such kind of nested triggers in past and it was really a nightmare for someone to understand what's happening without any proper documentation.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-10-16 : 11:16:42
|
| Visakh, I completely agree!!Way back when, around the time I was beginning to learn SQL, I once found myself in the uncomfortable situation where I had to update one row in a table in a production database. The real SQL programmer was on vacation and I was supposed to be the next best thing. But, unfortunately for me, this was my first foray into real SQL. I was still wet behind the ears - at a stage where it was barely beginning to dawn on me that not all SQL commands have to start "SELECT * FROM". After much googling and soul searching, I wrote the update statement, tested it carefully in a test environment with dummy tables etc., satisfied myself that it works as expected. Then, with shaking fingers and a prayer on my lips I clicked execute in the production environment. Imagine my surprise when the output window showed something like:1 row affected14758 rows affected38 rows affectedMy heart jumped into my mouth (but I swallowed it again), and reconciled to myself that it was my last day at work at that company (or at any company for that matter - who would hire someone who destroyed her employer's production database)? With a sour expression on my face I ran to my boss's office and told him that I may have cost him his job along with mine. He looked at the applications that should be affected. Nothing seemed amiss and in fact, the change I had done seemed to have worked.To make a long story short, we found that there were triggers on the table that I updated that caused the multiple updates that I saw. Ever since then, I am prejudiced against triggers of any sort and have hated nested/recursive triggers with a passion. |
|
|
|
global_erp_solution
Starting Member
28 Posts |
Posted - 2012-10-16 : 13:39:31
|
| Visakh,could you please post a sample of the approach you described in your post? it would be very helpful to me. Thanks |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
global_erp_solution
Starting Member
28 Posts |
Posted - 2012-10-18 : 02:11:55
|
| can anybody please give me an example where trigger is the appropriate solution? because I've goggled around and a lot of people suggest to avoid trigger. use constraint and stored procedure instead of triggers. So why do triggers exist? any sample scenario where trigger is better than the other approaches? thanks |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
|
|
|
trigger activation