Stopping multi row updates

• terry999

  SSCertifiable

  Points: 5036

  More actions

  August 17, 2011 at 9:57 am

  #243344

  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

• Orlando Colamatteo

  SSC Guru

  Points: 182279

  More actions

  August 17, 2011 at 3:31 pm

  #1369882

  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

• mister.magoo

  SSC-Forever

  Points: 47174

  More actions

  August 17, 2011 at 4:45 pm

  #1369894

  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);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]