August 17, 2011 at 9:57 am
Hi I'm porting an Access DB to SQL Server 2008.
Unfortunately there is an excel spreadsheet that gets sent to customers who fill it in, when we get it back we press a button on the XLS sheet and it updates one table on our Access DB.
I really don't want to get into how this sheet works, I can create an Access DB with linked tables to SQL using a FileDSN so the XLS sheet can continue to work without any code changes in Excel. This works but the user then has write permissions.
To overcome this
I can create a view for just the fields from the only table this XLS updates and a new user who only has rights on this single view.
Is it possible to limit updates/inserts on this view to single row updates? Shouldn't happen but just in case someone changes the Excel VBA.
The only way I can think of is an instead off trigger that checks rowcount of inserted table and errors if more than 1 row affected.
General Question about Instead of triggers
Is there any shorthand syntax rather than having to code the whole update/insert in the Instead of trigger
Thanks
August 17, 2011 at 3:31 pm
terryshamir (8/17/2011)
Hi I'm porting an Access DB to SQL Server 2008.Unfortunately there is an excel spreadsheet that gets sent to customers who fill it in, when we get it back we press a button on the XLS sheet and it updates one table on our Access DB.
I really don't want to get into how this sheet works, I can create an Access DB with linked tables to SQL using a FileDSN so the XLS sheet can continue to work without any code changes in Excel. This works but the user then has write permissions.
To overcome this
I can create a view for just the fields from the only table this XLS updates and a new user who only has rights on this single view.
Is it possible to limit updates/inserts on this view to single row updates? Shouldn't happen but just in case someone changes the Excel VBA.
The only way I can think of is an instead off trigger that checks rowcount of inserted table and errors if more than 1 row affected.
General Question about Instead of triggers
Is there any shorthand syntax rather than having to code the whole update/insert in the Instead of trigger
Thanks
I think you have it covered...an instead trigger on a VIEW will give you the control you're looking for. There is no way around coding the DML yourself. You might be able to use * to cut down on the code by shortening the column-list and make it tolerant of folks adding new columns...but TMK there is no shorthand to implementing those 🙂
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 17, 2011 at 4:45 pm
I wonder couldn't you use an AFTER trigger with a rollback? something like this:
CREATE trigger ForceOneRow on MyTable
AFTER INSERT
AS
-- Check for multiple rows, but only for a specific user
IF @@ROWCOUNT>1 AND User_Name()='ViewUser'
BEGIN
-- Record the transaction count so it can be restored after the rollback
DECLARE @tc INT;
SET @tc = @@TRANCOUNT;
-- now rollback the transaction and restore the transaction count so that we don't get the wrong error reported.
ROLLBACK TRAN;
WHILE @tc>0
BEGIN
BEGIN TRAN;
SET @tc = @tc - 1;
END;
-- And finally raise whatever error you like.
RAISERROR(N'Idiot! I said one row at a time!',16,0);
END;
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);
August 18, 2011 at 3:51 am
Mr Magoo (love the name by the way!!)
I didn't know you could roll back in an after trigger - I thought the change had been committed already.
August 18, 2011 at 4:45 am
No, as far as I know, it is still part of the transaction, so can be rolled back.
It certainly works for me anyway.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);
August 18, 2011 at 5:53 am
Works for me, but I had to make the trigger on the base table doesn't support after trigger on view.
One thing I did notice; I issued an update that would have affected say 30 rows out of a 1000 rec table. Got the Correct error msg back and data was not changed.
However it did say 1000 rows affected i.e. whole table - which it didn't?
August 18, 2011 at 6:10 am
terryshamir (8/18/2011)
Works for me, but I had to make the trigger on the base table doesn't support after trigger on view.One thing I did notice; I issued an update that would have affected say 30 rows out of a 1000 rec table. Got the Correct error msg back and data was not changed.
However it did say 1000 rows affected i.e. whole table - which it didn't?
The rows update message is because it is an after trigger, so the rowcount of inserting the rows is displayed before the trigger fires, then the transaction is rolled back so the rows are not there, but you don't get the corresponding "negative" row count of the rows being "removed".
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);
August 18, 2011 at 6:19 am
Thanks - oops my fault I was indeed updating whole table my where clause was nonsense.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply