Trigger / SPROC help?

[ninefoureight]

I'm trying to write a trigger and a sproc to achieve the following ;

table A has fields

name,value,date,pb
joe,45.6,2013-11-10,null
joe,23.1,2013-11-9,null
joe,56,2013-10-01,null
joe,12,2013-10-02,null

I am now going to insert the two records

joe,65,2012-09-01
joe,101,2013-10-07

On inserting the record i want to check if the if the value is higher than anything previous and reference the date to see if its later than the previous best and if it is update the pb column to yes.

I am thinking the trigger should qualify something like this

where name=@name
and date>@date
and value>@value

On completion of the trigger the PB column would be update to yes.

The next step (arguably the first and one off step) would be to recursively go trhough all the records exiting with a sproc and do the same line by line.

so eventually the table results with the new record would be

name,value,date,pb
joe,65,2012-09-01,pb
joe,56,2013-10-01,null
joe,12,2013-10-02,null
joe,101,2013-10-07,pb
joe,23.1,2013-11-9,null
joe,45.6,2013-11-10,null

Appreciate any input.
Cheers

 

[planty]

Assuming you're using MySQL and your value column is a DOUBLE...

I would create a function which returns the pb on a given date.

DELIMITER //

DROP FUNCTION IF EXISTS get_pb_on_date//
CREATE FUNCTION get_pb_on_date(_Date DATE)
RETURNS DOUBLE UNSIGNED
DETERMINISTIC
READS SQL DATA
BEGIN
	DECLARE returnVal DOUBLE UNSIGNED DEFAULT 0;
	SELECT MAX(value) INTO returnVal FROM mytable WHERE date < '_Date';
	RETURN returnVal;
END//

Then in a before insert trigger you can do something like:

IF NEW.value > get_pb_on_date(NEW.date) THEN
    SET NEW.pb = 1;