May 10, 2012 at 7:37 am
Eugene Elutin (5/10/2012)
...
Depends on the reason for it. If the reason for preventing DML is to avoid long locks when another process is running, an after trigger doesn't help at all...
Actually, how Instead Off trigger can help here? I can see it will only be helpful if you want to ignore DML not prevent it as such.
Until, Instead Of trigger writes everything into temporary storage area for a duration of restriction...
Hmm, my initial thought was that it would not hold locks on the target table until the instead of DML runs, but thinking this through, that's probably not the case as it would pose issues with transactional consistency. There's not a lot of documentation on the subject...
Maybe it would be better to enforce this by denying privileges to the user/role for the period of time - you wouldn't have the option of a custom error message, but that could be done at the application layer...
May 10, 2012 at 7:47 am
HowardW (5/10/2012)
Eugene Elutin (5/10/2012)
...
Depends on the reason for it. If the reason for preventing DML is to avoid long locks when another process is running, an after trigger doesn't help at all...
Actually, how Instead Off trigger can help here? I can see it will only be helpful if you want to ignore DML not prevent it as such.
Until, Instead Of trigger writes everything into temporary storage area for a duration of restriction...
Hmm, my initial thought was that it would not hold locks on the target table until the instead of DML runs, but thinking this through, that's probably not the case as it would pose issues with transactional consistency. There's not a lot of documentation on the subject...
Maybe it would be better to enforce this by denying privileges to the user/role for the period of time - you wouldn't have the option of a custom error message, but that could be done at the application layer...
Yeah, I guess requirements are not very clear...
If OP wants just to ignore DML, he needs INSTEAD OF trigger, but he needs to understand that any DML issued against this table will be lost until he will somehow log it outside of the table (it can be actually done as part of the trigger). I'm not sure if will help with locks...
If OP wants to stop DML from happening and be vocal about it, then there are few options:
AFTER trigger. or, let say some scheduled job which revokes/grants access to the table for a required time period. The second option would allow to differentiate rule by user, but will be less robust as job may fail for one or another reason. The first option would be at once and for all.
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply