May 24, 2018 at 12:50 am
Hi All,
I am really confused with Nested trigger and Recursive trigger.
I will put my understanding here which i gained from book and article, please correct me with examples if i am wrong .
1. Nested trigger is situation when initiated trigger tends to do some event on other table which in turn will fire another trigger and it goes on. But it stops when Nested loop tries to do event on the same table which initiated this nested trigger . Nested trigger by default enabled (via server configuration we can disable)
is the above sentence correct about trigger ? if it is correct then what is recursive trigger ?
2. Recursive trigger is situation when trigger fires itself in loop by doing some event to its own table (deep till 32 level) . Recursive by default it is disabled. (via DB configuration we can enable )
if the above sentence is correct then how 1st point is correct? I mean how trigger stops loop when it tries to fire itself in nested trigger?
Sorry for my bad English. I am really confused about this and couldn't get clear answer about this in any site.
May 24, 2018 at 5:13 am
1. Nested triggers fire triggers on other tables, views etc, not on its own table, also as this is enabled in the configuration, it is something it is possible to do (not generally advised though as can cause massive issues if not created and maintained correctly)
2. Recursive is set to off by default, so you can't use it unless you turn it on. If you do turn it on, you can make a single trigger recursive, it doesn't have to call anything else (again, why you'd want to do this, I can't answer).
May 24, 2018 at 6:57 am
Rick-153145 - Thursday, May 24, 2018 5:13 AM1. Nested triggers fire triggers on other tables, views etc, not on its own table, also as this is enabled in the configuration, it is something it is possible to do (not generally advised though as can cause massive issues if not created and maintained correctly)2. Recursive is set to off by default, so you can't use it unless you turn it on. If you do turn it on, you can make a single trigger recursive, it doesn't have to call anything else (again, why you'd want to do this, I can't answer).
Thanks for your clarity.
But
lets take this scenario :
Recursive trigger is enabled and nested trigger also enabled.
Now Nested trigger looping occurred and at some point the same trigger is trying to trigger itself i.e., initiated trigger trying to call itself. what will happen ? will the execution stop or it will do recursive ?
This is what my question is .. I will be happy if i get correct answer. if possible with example.
May 24, 2018 at 9:08 am
Never tried it, but recursive is different to nested in the respect of nested being more than one trigger, recursive means making a single trigger run more than once.
May 25, 2018 at 8:42 am
JoNTSQLSrv - Thursday, May 24, 2018 6:57 AMRick-153145 - Thursday, May 24, 2018 5:13 AM1. Nested triggers fire triggers on other tables, views etc, not on its own table, also as this is enabled in the configuration, it is something it is possible to do (not generally advised though as can cause massive issues if not created and maintained correctly)2. Recursive is set to off by default, so you can't use it unless you turn it on. If you do turn it on, you can make a single trigger recursive, it doesn't have to call anything else (again, why you'd want to do this, I can't answer).
Thanks for your clarity.
But
lets take this scenario :
Recursive trigger is enabled and nested trigger also enabled.Now Nested trigger looping occurred and at some point the same trigger is trying to trigger itself i.e., initiated trigger trying to call itself. what will happen ? will the execution stop or it will do recursive ?
This is what my question is .. I will be happy if i get correct answer. if possible with example.
If you have to ask that question, you really, really, really do NOT want to find out in a real database what that can do to you. My first caution is that the reason you have to turn those features on is because they are inherently dangerous. They really should be avoided if at all possible. Too much risk. If you design a database around using that kind of functionality, I would generally be questioning your judgement...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply