CODEWITHSUNDEEP
mysqlviewtriggersmysql-workbench
FrancescoN
FrancescoN

Reputation: 2186

Create View in a Trigger

I have to use a view (not already created, because it's needed within that UPDATE TRIGGER) in an UPDATE inside an UPDATE TRIGGER.

So this is my code:

CREATE TRIGGER lunghezza_sentiero_datoderivato_UPDATE2
AFTER UPDATE ON TAPPA
FOR EACH ROW
BEGIN

CREATE VIEW tempoOLD_NULL(IDsentiero, tempo) AS 
    SELECT IDsentiero, tempo
    FROM SENTIERO
    WHERE tempo IS NULL and SENTIERO.IDsentiero IN (SELECT DISTINCT IDsentiero
                                                    FROM SENTIERO__HA__TAPPA AS sht
                                                    WHERE NEW.IDtappa=sht.IDtappa);

CREATE VIEW lunghezzaOLD_NULL(IDsentiero, lunghezza) AS 
    SELECT IDsentiero, lunghezza
    FROM SENTIERO
    WHERE lunghezza IS NULL and SENTIERO.IDsentiero IN (SELECT DISTINCT IDsentiero
                                                        FROM SENTIERO__HA__TAPPA AS sht
                                                        WHERE NEW.IDtappa=sht.IDtappa);

IF (NEW.lunghezza<>OLD.lunghezza) and (NEW.lunghezza is not null) THEN

    UPDATE SENTIERO
    SET lunghezza=lunghezza - OLD.lunghezza + NEW.lunghezza
    WHERE IDsentiero IN (SELECT DISTINCT IDsentiero
                         FROM SENTIERO__HA__TAPPA AS sht
                         WHERE NEW.IDtappa=sht.IDtappa) and IDsentiero NOT IN (SELECT IDsentiero
                                                                               FROM lunghezzaOLD_NULL);

    UPDATE SENTIERO
    SET lunghezza=NEW.lunghezza
    WHERE IDsentiero IN (SELECT IDsentiero
                         FROM lunghezzaOLD_NULL);



END IF;

When i COMMENT both 2 CREATE VIEW all runs... but i need that views.. so, please help me! (create view outside the trigger? but i need to create a view on every update, because the NULL value change along the time)

ADDENDUM FOR TABLES AND EXAMPLES AS REQUESTED:

CREATE  TABLE IF NOT EXISTS `SENTIERO` (
  `IDsentiero` INT(11) NOT NULL COMMENT 'identificativo del sentiero' ,
  `lunghezza` FLOAT NULL COMMENT 'dato derivato' ,
  `tempo` FLOAT NULL COMMENT 'dato derivato' ,
  PRIMARY KEY (`IDsentiero`) );

CREATE  TABLE IF NOT EXISTS `TAPPA` (
  `IDtappa` INT(11) NOT NULL COMMENT 'identificativo tappa' ,
  `lunghezza` FLOAT NULL COMMENT 'Lunghezza espressa in km' ,
  `tempo` FLOAT NULL COMMENT 'La quantità di ore stimate per arrivare alla tappa' ,
  PRIMARY KEY (`IDtappa`) );

CREATE  TABLE IF NOT EXISTS `SENTIERO__HA__TAPPA` (
  `IDtappa` INT(11) NOT NULL ,
  `IDsentiero` INT(11) NOT NULL ,
  PRIMARY KEY (`IDtappa`, `IDsentiero`) );

One 'TAPPA' can be included in one or more 'SENTIERO', one 'SENTIERO' includes one or more 'TAPPA'.

Each 'SENTIERO' has the sum of 'tempo' and 'lunghezza' of its one or more 'TAPPA'. So when occurs an update on 'TAPPA', if 'tempo' and 'lunghezza' of that 'TAPPA' have changed, i want to re-calculate the 'tempo' and 'lunghezza' for the one or more 'SENTIERO' the updated 'TAPPA' is related to.

See, 'SENTIERO' has the SUM of its own 'TAPPA'

SENTIERO                     |    SENTIERO_HA_TAPPA       |  TAPPA
IDsentiero tempo lunghezza   |    IDsentiero  IDtappa     |  IDtappa tempo lunghezza  
   0         3     20        |        0          1        |     1      3      20
   1         8     35        |        1          1        |     2      5      15
                             |        1          2        |

Upvotes: 0

Views: 4516

Answers (1)

eggyal
eggyal

Reputation: 125965

Don't use views for this purpose. It's not what they're intended for.

Instead, learn how to join tables properly:

CREATE TRIGGER lunghezza_sentiero_datoderivato_UPDATE2
AFTER UPDATE ON TAPPA
FOR EACH ROW

  IF (NEW.lunghezza <> OLD.lunghezza) AND (NEW.lunghezza IS NOT NULL) THEN
    -- note that this condition will not be entered if `OLD.lunghezza` IS NULL
    -- irrespective of NEW.lunghezza.  Perhaps you want to use the NULL-safe
    -- equality operator <=> instead?

    UPDATE SENTIERO AS s
      JOIN SENTIERO__HA__TAPPA AS sht USING (IDsentiero)
    SET    s.lunghezza = CASE
             WHEN s.lunghezza IS NULL THEN NEW.lunghezza
             ELSE s.lunghezza - OLD.lunghezza + NEW.lunghezza
           END
    WHERE  sht.IDtappa = NEW.IDtappa;

  END IF

Upvotes: 1

Related Questions