Delete row
-
I have a table in SQL Express 2005 that I would like to delete the first row after a specified number of rows have been added. The table contains four columns, one of the column contains the ID number starting at 1 and is autoincremented when a new row is added. Before a new row is added, I want to check if there are already 60 rows and delete the first row before adding the new row. I want new rows to be added, but want to keep the number of rows at 60 by deleting the oldest rows. I would really appreciate it if someone can provide with an example of this.
-
I have a table in SQL Express 2005 that I would like to delete the first row after a specified number of rows have been added. The table contains four columns, one of the column contains the ID number starting at 1 and is autoincremented when a new row is added. Before a new row is added, I want to check if there are already 60 rows and delete the first row before adding the new row. I want new rows to be added, but want to keep the number of rows at 60 by deleting the oldest rows. I would really appreciate it if someone can provide with an example of this.
may u can use triggers like this:
CREATE TRIGGER myTrigger ON myTable FOR INSERT AS /* here check the count of rows and delete smaller id*/ BEGIN DECLARE @count int; SELECT @count = COUNT(*) FROM myTable IF(@count>60) BEGIN DECLARE @first int; SELECT @first = MIN(ID) FROM myTable DELETE FROM myTable WHERE ID=@first END ENDand if you want to have your ID column Identity and only from 1-60 use IDENTITY_INSERT check msdn and web for more
I Wish the Life Had CTRL-Z