[GENERAL] Creating rule for sliding data

Hello,

I have a problem to find the good syntax for a rule for rows going for 
one partition to the other in cas of an update.

Let me give the conditions :

1 - having a mother table

CREATE TABLE T_MESURE_MSR
(  MSR_ID      INT   NOT NULL,  MSR_DATE    DATE  NOT NULL,  MSR_MESURE  FLOAT NOT NULL
);

2 - having 2 child table :

CREATE TABLE T_MESURE_BEFORE2000_MSR
(
CHECK ( MSR_DATE < DATE '2000-01-01')
) INHERITS (T_MESURE_MSR)

CREATE TABLE T_MESURE_AFTER1999_MSR
(
CHECK ( MSR_DATE >= DATE '2000-01-01')
) INHERITS (T_MESURE_MSR)

THE QUESTION...

How to make the proper rule for T_MESURE_MSR il a rox goes from 1998 to 
2003 ?

This one does not work :

CREATE RULE R_U_MSR_BEFORE2000
AS
ON UPDATE TO T_MESURE_MSR   WHERE ( NEW.MSR_DATE < DATE '2000-01-01' )
DO INSTEAD
-- rows does not change partition :  UPDATE T_MESURE_BEFORE2000_MSR  SET MSR_ID     = NEW.MSR_ID,      MSR_DATE   =
NEW.MSR_DATE,     MSR_MESURE = NEW.MSR_MESURE  WHERE ( OLD.MSR_DATE < DATE '2000-01-01' );
 

-- rows does change partition (first INSERT NEWs then DELETE OLDs)  INSERT INTO T_MESURE_MSR  VALUES ( NEW.MSR_ID,
    NEW.MSR_DATE,           NEW.MSR_MESURE )  WHERE NOT ( OLD.MSR_DATE < DATE '2000-01-01' );
 
  DELETE FROM T_MESURE_MSR  WHERE  MSR_ID = OLD.MSR_ID    AND  MSR_DATE = OLD.MSR_DATE    AND  MSR_MESURE =
OLD.MSR_MESURE WHERE NOT ( OLD.MSR_DATE < DATE '2000-01-01' );
 


And no more for this one :

CREATE RULE R_U_MSR_BEFORE2000
AS
ON UPDATE TO T_MESURE_MSR   WHERE ( NEW.MSR_DATE < DATE '2000-01-01' )
DO INSTEAD
-- rows does not change partition :  UPDATE T_MESURE_BEFORE2000_MSR  SET MSR_ID     = NEW.MSR_ID,      MSR_DATE   =
NEW.MSR_DATE,     MSR_MESURE = NEW.MSR_MESURE  WHERE ( OLD.MSR_DATE < DATE '2000-01-01' );
 

-- rows does change partition (first INSERT the NEWs then DELETE the OLDs)  INSERT INTO T_MESURE_MSR  SELECT MSR_ID,
    MSR_DATE,         MSR_MESURE  FROM   NEW  WHERE NOT ( OLD.MSR_DATE < DATE '2000-01-01' );
 
  DELETE FROM T_MESURE_MSR  WHERE  (MSR_ID, MSR_DATE, MSR_MESURE)         IN (SELECT MSR_ID, MSR_DATE, MSR_MESURE
     FROM   OLD             WHERE NOT ( OLD.MSR_DATE < DATE '2000-01-01' ));
 

Any idea ?

Thanks


-- 
Frédéric BROUARD - expert SGBDR et SQL - MVP SQL Server - 06 11 86 40 66
Le site sur le langage SQL et les SGBDR  :  http://sqlpro.developpez.com
Enseignant Arts & Métiers PACA, ISEN Toulon et CESI/EXIA Aix en Provence
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
*********************** http://www.sqlspot.com *************************