CODEWITHSUNDEEP
mysqltriggers
Kanted
Kanted

Reputation: 169

create a trigger need to update table 2 on update of table1

Would like to create a trigger in mysql need to update t2.orderstatus as 'inactive' on update of t2.inactivedate and update t2.orderstatus as 'active' on update of t2.activedate. Tried to adapt below one. but failed 

DROP TRIGGER IF EXISTS trigger_on_stockhistory_update;
DELIMITER $$
CREATE TRIGGER trigger_on_stockhistory_update
AFTER update ON stockhistory.inactivedate
FOR EACH ROW
BEGIN
    UPDATE  mastersku
    SET ordernow ='InActive'
    WHERE   `SSKU`  = NEW.SSKU;

END;
$$
DELIMITER;

Upvotes: 0

Views: 44

Answers (1)

Slava Rozhnev
Slava Rozhnev

Reputation: 10163

You can not set trigger on field change. It may be only on table level: 

DROP TRIGGER IF EXISTS trigger_on_stockhistory_update;
DELIMITER $$

CREATE
    TRIGGER `trigger_on_stockhistory_update` AFTER UPDATE
    ON `stockhistory`
    FOR EACH ROW BEGIN
    IF OLD.inactivedate <> NEW.inactivedate THEN
        UPDATE  mastersku
        SET ordernow ='InActive'
        WHERE   `SSKU`  = NEW.SSKU;
    END IF;

    IF OLD.activedate <> NEW.activedate THEN
        UPDATE  mastersku
        SET ordernow ='Active'
        WHERE   `SSKU`  = NEW.SSKU;
    END IF;
    END$$

DELIMITER ;

Or a little elegant query using CASE statement:

DROP TRIGGER IF EXISTS trigger_on_stockhistory_update;
DELIMITER $$

CREATE
    TRIGGER `trigger_on_stockhistory_update` AFTER UPDATE
    ON `stockhistory`
    FOR EACH ROW BEGIN
        UPDATE  mastersku
        SET ordernow = CASE
            WHEN OLD.inactivedate <> NEW.inactivedate THEN 'InActive'
            WHEN OLD.activedate <> NEW.activedate THEN 'Active' 
            ELSE ordernow
        END
        WHERE
          SSKU = NEW.SSKU;

    END$$

DELIMITER ;

Upvotes: 1

Related Questions