MySQL – Simple Trigger to Update Two Columns

MySQLtrigger

I want to create a trigger MySQL that updates two fields at the same time.

Whenever a record is added or inserted, I want the contents to be copied to one field or the other, depending on which one is empty/edited.

due_date is a varchar and end_date is a datetime.

e.g.

IF new record with due_date filled in but end_date empty → copy due_date to end_date after insert.
IF new record with end_date filled in but due_date empty → copy end_date to due_date after insert.
IF update to due_date → copy that to end_date after update
IF update to end_date → copy that to due_date after update

Please note, the due_date format is already the correct and same format as datetime.

Best Answer

Hope i have understand what you are saying correctly, following would be the code you may be looking for.

DELIMITER //
CREATE TRIGGER trig_1 before insert
ON  <table_name> FOR EACH ROW
BEGIN

   IF   new.due_date is not null and new.end_date='' then
        set new.end_date=new.due_date;
   end if;
      IF   new.end_date is not null and new.due_date='' then
        set new.due_date=new.end_date;
   end if;

END;
//


DELIMITER //

CREATE TRIGGER trig_2 before update
ON  <table_name> FOR EACH ROW
BEGIN 

   IF   new.due_date <>old.due_date then
        set new.end_date=new.due_date;
   end if;
      IF   new.end_date <> old.end_date then
        set new.due_date=new.end_date;
   end if;

END;
//

Hope it helps

Related Solutions

MySQL – How to Disable Trigger for One Table

Actually, if you place an if then block in every trigger, you could effectively shutdown all triggers. Here is such a code block

IF @TRIGGER_DISABLED = 0 THEN
    ...trigger body
END IF;

In the mysql environment, you could

run SET @TRIGGER_DISABLED = 1;
do your data maintenance
run SET @TRIGGER_DISABLED = 0;

So your trigger for table A should look like this:

BEGIN 
    IF @TRIGGER_DISABLED = 0 THEN
    IF (OLD.status != 1 AND NEW.status = 2) THEN 
        IF (OLD.geo_lat IS NOT NULL AND OLD.geo_long IS NOT NULL) THEN 
            DELETE FROM geo WHERE datatype IN (3,4) AND foreignid = NEW.id; 
        END IF; 
    ELSEIF (OLD.Status = 1 AND NEW.Status != 2) THEN 
        IF (NEW.geo_lat IS NOT NULL AND NEW.geo_long IS NOT NULL) THEN 
            INSERT INTO geo (datatype, foreignid, long, lat, hostid, morton, status) VALUES  (IF(NEW.groupType=1,3,4), NEW.id, NEW.geo_long, NEW.geo_lat, NEW.hostid, 0, NEW.Status); 
        END IF; 
    ELSEIF (NEW.status != 3) THEN   
        IF (OLD.geo_lat IS NOT NULL AND OLD.geo_long IS NOT NULL AND (NEW.geo_lat IS NULL OR NEW.geo_long IS NULL)) THEN 
            DELETE FROM geo WHERE datatype IN (3,4) AND foreignid = NEW.id; 
        ElSEIF ((OLD.geo_lat IS NULL OR OLD.geo_long IS NULL) AND NEW.geo_lat IS NOT NULL AND NEW.geo_long IS NOT NULL) THEN 
            INSERT INTO geo (datatype, foreignid, longitude, latitude, hostid, morton, status) VALUES  (IF(NEW.groupType=1,3,4), NEW.id, NEW.geo_long, NEW.geo_lat, NEW.hostid, 0, NEW.Status); 
        ELSEIF (OLD.geo_lat!=NEW.geo_lat OR OLD.geo_long != NEW.geo_long OR OLD.status != NEW.status) THEN 
            UPDATE geo SET lat = NEW.geo_lat, long = NEW.geo_long, status = NEW.status WHERE datatype IN (3,4) AND foreignid = NEW.id; 
        END IF; 
    END IF; 
    END IF; 
END

So your trigger for table B should look like this:

CREATE TRIGGER `usergroups_comments_insert` AFTER INSERT ON `usergroups_comment`     
    FOR EACH ROW     
    BEGIN     
        IF @TRIGGER_DISABLED = 0 THEN
            CALL sp-set-comment_count(NEW.`gid`);     
        END IF;
    END;

If you want the triggers for table A to launch but not to table B, then add the code block only to table B's trigger.

Mysql – Is it possible for a trigger to update a row in a table using a value that was deleted

If I understand your question right, what you want is an after delete trigger:

DELIMITER $$
CREATE TRIGGER ad_foo AFTER DELETE on foo
FOR EACH ROW
BEGIN
  update table1 SET table1.state=1 WHERE id=OLD.id;
END$$
DELIMITER ;

Example with just the insert: http://sqlfiddle.com/#!2/974b4/1

Example with insert and delete: http://sqlfiddle.com/#!2/0bf72/1

Best Answer

Related Solutions

MySQL – How to Disable Trigger for One Table

Mysql – Is it possible for a trigger to update a row in a table using a value that was deleted

Related Question