SQL Using triggers and SPs to limit records for a particular column

[enitial]

Hi

I am developing a SQL database that will sync with handhelds used to collect data in the field. This will be a two way sync, as I want all handhelds to hold previous data, so the handhelds can run statistical analysis on new data, before excepting. This is ok, but I want to limit the data the handhelds recieve.

The handhelds will sync to a view of a table with the structure:

SELECT OID, sBHVisitID, sSiteVisitID, sSamplePointID, sSiteName, dDateTime, fDTW, fDTB, bSampled, sSampleMethod, fPurgeVol, fpH, fTemp, lEC, lPulseCount, fFlow, sComments, oScribbleNote, sGPSLat, sGPSLong, sGPSAlt, bProcessed, bDeactivated, bArchived, bDeleted, fDO, sPersonnel, sPersonnelUpdated, sDeviceID, sDeviceIDUpdated, dUpdated, TIMESTAMP
FROM dbo.VBHVisit
WHERE (bDeactivated = 0) AND (bArchived = 0) AND (bDeleted = 0)

What I would like to do is add a way that when a new INSERT statement is executed on the underlying table, a procedure executes to count the number of records for each sSamplePointID now visible in this view (preferably just for the sSamplePointIds affected as each record is inserted), sorted by dDateTime, and where there are more than 5 records for each sSamplePointID, update their bArchived to 'true' for all but the 5 most recent records. This will cause the now archived records to drop out of the view.

Thanks for reading, your help is very appreciated.

John

 

[SQLSister]

You could do this with a trigger, but can't you just select the top 5 based on datetime desc instead?

"NOTHING is more important in a database than integrity." ESquared

 

[NastiaMurzin]

select * from tablename where bArchived != '%true%' order by dDateTime;
select count(sSamplePointID) from tablename;
I hope it works.

 

[enitial]

Thanks you both for your input.

SQLSister: My SQL knowledge is limited, so please let me know if I am missing something; but the problem here is I would end up with a limited number of records in total, wouldn't I? - say 5 for the whole query return. What I need is 5 records returned for each unique Sample Point [sSamplePointID] The possible number of Sample Points is infinate.

Nastia: The problem here is I need a way of automatically flagging records as archived. As soom as a record for a unique Sample Point is superseed by 5 newer datasets for that same Sample Point, it needs to be either marked as 'Archived' (in which case your proc would be ideal) or there needs to be another proc that can filter these historic records out.

Sorry to be a pain! But thanks so much for taking the time to look at my problem.

John

 

[NastiaMurzin]

use tableschema;
go
create procedure t
if table dbo.Wings_IVR_CallInfromation is not null
drop table dbo.Wings_IVR_CallInformation;
create table dbo.Wings_IVR_CallInformaion
(id int as primary key not null auto_increment,
ivr_msg varchar(100) null);
go
insert into dbo.Wings_IVR_CallInformation values ('some message for ivr_msg column');
go
select count(ivr_msg) from dbo.Wings_IVR_CallInformation;
go

select * from tablename where bArchived != '%true%' order by dDateTime;
go
select count(sSamplePointID) from tablename;
go

I hope it works.