Re: [GENERAL] Creating rule for sliding data

I am answering to myseilf...

the good syntax is something like :


CREATE RULE R_U_MSR_BEFORE2000x
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  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 = OLD.MSR_ID    AND  MSR_DATE = OLD.MSR_DATE    AND  MSR_MESURE = OLD.MSR_MESURE    AND  NOT (
OLD.MSR_DATE< DATE '2000-01-01' );
 
);

The problem is nowhere in the doc there is a mention where much more 
than one commande must be place into brackets !

A +

-- 
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 *************************