Limiting the number of updates on a table using Trigger

• John Burchel

  Hall of Fame

  Points: 3611

  More actions

  September 4, 2006 at 10:42 am

  #62270

  Hi All,

   

  I was wondering if there was a way in which a particular user can be audited for updates on a table, eg

  I normally have users performing updates without using the where clause, I was wondering if there was a trigger in which I can use to check if a user has updated more than 10 rows, then rollback and throw an error message saying, ohhh, speak to Manager.

  Thanks in advance

   

   

  ---

  Kindest Regards,

  John Burchel (Trainee Developer)
  

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  September 4, 2006 at 11:08 am

  #658402

  You need something like this :

  CREATE

  TRIGGER dbo.trTableName_F_U ON dbo.TableName

  FOR

  UPDATE

  AS

  SET NOCOUNT ON

  IF 10 < (SELECT COUNT(*) FROM DELETED)

  BEGIN

  ROLLBACK TRANSACTION

  RAISERROR ('Cannot update more than 10 rows at the time on table TableName', 13, 1)

  END

  GO

• John Burchel

  Hall of Fame

  Points: 3611

  More actions

  September 4, 2006 at 11:27 am

  #658405

  Thanks for this, worked a treat

  ---

  Kindest Regards,

  John Burchel (Trainee Developer)