Delete the Old record before/after insert the record in a table, iff the record count is > 2

Question

Delete the Old record in a Table before/after insert the record into that table, and delete the old record only when the record count is greater than Two.

How to achieve the following Scenario:

Table Name : Sample

Id  Name    Date    Position
1   phani   2013    Trainee
2   phani   2014    Developer

Now we are Inserting One more record for phani

i.e 3 phani 2015 sr.Developer

Result:

Id  Name    Date    Position
1   phani   2013    Trainee
2   phani   2014    Developer
3   phani   2015    sr.Developer

Now we have to create one trigger, that trigger has to delete the old record
(means 2013 year record has to be delete.)

Expected OutPut :

Id  Name    Date    Position
2   phani   2014    Developer
3   phani   2015    sr.Developer

Now let's say in 2016 again the position changed from sr.developer to Project Manager.

That time 2014 year record has to be delete and insert new record,like Below :

Id  Name    Date    Position
3   phani   2015    sr.Developer
4   phani   2016    Project Manager

Answer 1

Sounds like you already know our answer. Create an after insert trigger that checks if the number of rows with given name is larger than two. If so find the oldest date matching this name and delete all rows with that date and name.

Answer 2

Assuming that you are using SQL Server as Database server.following query will yield result what you looking for. You have to just wrap this logic into trigger definition.

DELETE T1 FROM 
TableName T1 
WHERE T1.[Date] = ( SELECT MIN([Date]) 
                    FROM TableName T2 
                    WHERE T1.ID = T2.ID 
                    HAVING COUNT(*) > 2 )