I have created Product entity in MDS. It's having the following values: Bike 1 ABC Car 2 XYZ Cycle 3 RRR
owner XYZ can change the record of RRR. But if owner XYZ or any other owner in this entity tried to update the ABC's record, access should be denied. That means, no one should have the permission to change the records which are entered by ABC. for this I have executed following :
CREATE TRIGGER mdm.product_readonly
ON mdm.tbl_1034_1215_en
AFTER UPDATE, INSERT AS
If exists (system_user!='ABC')
and EXISTS (SELECT * FROM deleted a, inserted b, mdm.vw_product c
WHERE a.code=c.code
or b.code=c.code
And c.owner = 'ABC')
BEGIN
ROLLBACK TRANSACTION
RAISERROR ('Attempt to change a read-only row', 16, 1)
RETURN
END
After executing this, If I trying to update the record as ABC owner I can update all the records. If I try to update as other than ABC owner I couldnt update the ABC record. But from MDS I couldnt update any of the record. It showing like Database error.
How can we achieve this. Please help me out in this regard. Thanks!