exception_access_violation
-
I was just writing a simple trigger on a non updateable view so that I could update the base tables directly. When I tried to save the trigger (in enterprise manager) I got the following error
Error 5:[SQL-DMO]Code execution exception: EXCEPTION_ACCESS_VIOLATION
THe trigger was as follows:
CREATE TRIGGER updateschedule ON [dbo].[QryProdSched]
instead of update, insert
AS
ALTER TABLE [order detail] DISABLE TRIGGER ALL
update [order detail] set [reschedule date] = (select [reschedule date] from inserted)
ALTER TABLE [order detail] ENABLE TRIGGER ALL
any ideas why this occured?
-
Not sure about the access violation but I can tell you there are a lot of things wrong with that code (no offence)
:1.It is NOT a good practice to enable or disable triggers from a trigger !
2. You are asuming a single user scenario. Supposed there are more than one user trying to run your code. The first fire the disable trigger statement then carries on to try the update (more on this below) then suppose someone is affecting that table (order details) outside of your trigger. Because at the time the triggers are all disabled they won't fire and you (the second user) may not get what you wanted in the first place.
3. You are also assuming that only one row will be affected in the case of the update statement! If that is not the case the update will produce unexpected results
The correct Update statement should be:
Update O Set [reschedule date] = i.[reschedule date]
from
[order detail] O
join
inserted i
on i.pkey = O.pkey --- Don't know The actual link
Remember: Triggers should be always coded to handle MULTIPLE rows
* Noel
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply