Forum > Database

[SOLVED]DATASET Doesn't Filter as Expected

<< < (14/14)

kito:

--- Quote from: Zvoni on July 21, 2023, 07:21:17 pm ---Did you refresh everything?

--- End quote ---
you are my hero!

Everything worked smoothly and correctly. Now, I can get rid of the visual clutter I created in my program

alpine:
@Zvoni
There is an issue with the trgVACAftUpd trigger. It only increments the VAC2.OS and if you update VAC2.ST or VAC2.CO several times then VAC2.OS ends up with incorrect value.

Instead of using that clever recursive increase, I would propose the following triggers (using recursive CTE as alternative):

--- Code: SQL  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---CREATE TRIGGER tVACai         AFTER INSERT            ON VAC2      FOR EACH ROWBEGIN    UPDATE VAC2       SET OS = (           WITH nwd (st)               AS (                   SELECT O.ST                     FROM vwNonWorkingDays AS O                     WHERE DATE(O.ST) = DATE(JulianDay(NEW.ED) + 1)                    UNION ALL                   SELECT O.ST                     FROM vwNonWorkingDays AS O                          INNER JOIN                          nwd ON DATE(O.ST) = DATE(JulianDay(nwd.st) + 1)                )               SELECT COALESCE(JulianDay(MAX(st) ) - JulianDay(NEW.ED), 0)                  FROM nwd           )     WHERE ID = NEW.ID;END;

--- Code: SQL  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---CREATE TRIGGER tVACau         AFTER UPDATE            ON VAC2      FOR EACH ROW          WHEN OLD.ED <> NEW.EDBEGIN    UPDATE VAC2       SET OS = (           WITH nwd (st)               AS (                   SELECT O.ST                     FROM vwNonWorkingDays AS O                     WHERE DATE(O.ST) = DATE(JulianDay(NEW.ED) + 1)                    UNION ALL                   SELECT O.ST                     FROM vwNonWorkingDays AS O                          INNER JOIN                          nwd ON DATE(O.ST) = DATE(JulianDay(nwd.st) + 1)                )               SELECT COALESCE(JulianDay(MAX(st) ) - JulianDay(NEW.ED), 0)                  FROM nwd           )     WHERE ID = NEW.ID;END;
Computing OS in triggers raises the issue what if the holiday table changed in a way that affects the existing rows in VAC2.

Zvoni:
Interesting. Didn’t think about that.
Nicely done

alpine:

--- Quote ---Computing OS in triggers raises the issue what if the holiday table changed in a way that affects the existing rows in VAC2.
--- End quote ---

Still my opinion is that these should be calculated at the time of reading, i.e. not stored, to reflect the current data into the holiday table. A covering VIEW can be used to extend the VAC2 table with the calculated ED, DPREP. Pity is the VIEW is read-only in sqlite and it will require INSTEAD OF triggers to update the underlying data.

Zvoni:
True, but i had to use STORED for ED to make it available for the New qualifier, otherwise i would have to implement the whole calculation for ED

Navigation

[0] Message Index

[*] Previous page

Go to full version