March 16, 2018 at 3:46 am
I'm trying to enforce business logic by preventing data modifications on certain tables other than through SPs. There is a small set of users who have access to the data when they attempt any modification they should see a message pointing them in the direction of the relevant SP.
I've created Instead of Triggers which, for inset and update work fine. The Instead of Delete Trigger prevents modifications but fails to display any message.
I'm guessing this is something to do with the nature of the transaction but I don't know what. ALTER TRIGGER [dbo].[trg_stop_Updates]
ON [dbo].[ee] INSTEAD OF UPDATE, INSERT AS
BEGIN
RAISERROR ('You Shall Not Pass',16,1)
ROLLBACK TRANSACTION;
END;
Any help or suggestions gladly accepted.
This is a high-class Bureau-de-Change.
March 16, 2018 at 5:20 am
Even stranger. This behaviour seems to be limited to my local 2014 instance, it isn't occurring on a 2016 Dev box. Must, presumably, be an environmental issue or a setting.
No longer urgent, but would be interested to hear anyone's theories or suggestions.
This is a high-class Bureau-de-Change.
March 16, 2018 at 7:23 am
five_ten_fiftyfold - Friday, March 16, 2018 3:46 AMI'm trying to enforce business logic by preventing data modifications on certain tables other than through SPs. There is a small set of users who have access to the data when they attempt any modification they should see a message pointing them in the direction of the relevant SP.I've created Instead of Triggers which, for inset and update work fine. The Instead of Delete Trigger prevents modifications but fails to display any message.
I'm guessing this is something to do with the nature of the transaction but I don't know what.
ALTER TRIGGER [dbo].[trg_stop_Updates]
ON [dbo].[ee] INSTEAD OF UPDATE, INSERT ASBEGIN
RAISERROR ('You Shall Not Pass',16,1)
ROLLBACK TRANSACTION;
END;
Any help or suggestions gladly accepted.
Revers the order of the two statements in the BEGIN / END block. You're raising an error and then rolling it back.... I don't know why the behavior isn't seen in the other instance.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 16, 2018 at 7:39 am
Thanks, Steve. That's done the trick. You're right, it doesn't explain why the others are working but it will do for me.
This is a high-class Bureau-de-Change.
March 16, 2018 at 8:03 am
five_ten_fiftyfold - Friday, March 16, 2018 7:39 AMThanks, Steve. That's done the trick. You're right, it doesn't explain why the others are working but it will do for me.
You're welcome. And you might even decide to change the message of the error to be "Oh NO you DON'T!"
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 16, 2018 at 9:11 am
sgmunson - Friday, March 16, 2018 8:03 AMfive_ten_fiftyfold - Friday, March 16, 2018 7:39 AMThanks, Steve. That's done the trick. You're right, it doesn't explain why the others are working but it will do for me.You're welcome. And you might even decide to change the message of the error to be "Oh NO you DON'T!"
Actually, it should be "None shall pass"...
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply