How to use Case-When and Update in Trigger?(Error in my code)

A couple of notes:

A trigger cannot issue an UPDATE against the table that fired the trigger, this is prohibited.

The unqualified references (e.g. FUSDate1 and FUZDate1) are not valid. Those references don't resolve to anything.

The reference to OLD.FUSDate1 is invalid in the context of an AFTER INSERT trigger. In an UPDATE trigger, the OLD.col refers to the value of col before the update.

The expression (FUZDate1=OLD.FUSDate1) is not an assignment, it's a comparison that's going to return 0, 1 or NULL.

If we want to trigger on an UPDATE and an INSERT, that will require two separate triggers.

If we want to modify the contents of the current row, we can use BEFORE trigger instead of AFTER.

I want to update my existing row after insert or update.

It would be much easier to apply changes before the row is inserted or updated. We can assign a value to a column col by referencing NEW.col in an assignment in a BEFORE INSERT or BEFORE UPDATE trigger. For example:

 SET NEW.col = expr;

Do not change the FUSDate1 or FUSDate2 If I didn't add anything to the FUSDate1 or FUSDate2

This seems pretty straightforward. Just don't make any assignments to NEW.FUSDate1 or NEW.FUSDate2.

Update the FUSDate to the New One or Inserted one If I Updated or inserted data to FUSDate

The example proposed trigger contains references to FUZDate1 or FUZDate2, but there's no mention of these columns in the specification. The specification is confusing.

An UPDATE statement will assign a value to a column, no need for a trigger to do that. An INSERT statement can assign a value to a column, again, no need for a trigger for that.

The specification is not clear. Providing example starting state (rows in the table), and example INSERT or UPDATE statements, and the desired state after the statement is executed would go a long ways towards clarifying the requirements.

A demonstration of a BEFORE UPDATE trigger that prevents a new value being assigned to a particular column:

DELIMITER $$

CREATE TRIGGER  SafetyCertification_bu
BEFORE UPDATE ON   SafetyCertification
FOR EACH ROW
BEGIN
   -- detect a change made to a value in col
   IF OLD.col <=> NEW.col THEN
      -- value of col is not changed, so do nothing
      DO 0;
   ELSE
      -- we detected a new value was assigned to col
      IF OLD.col IS NOT NULL THEN
         -- we can override the new value, keep it the same
         SET NEW.col = OLD.col;
      END IF;
   END IF;
END$$

DELIMITER ;

For what this example trigger achieves, we don't necessarily need that many IF conditions; those are included as a demonstrate some of the checks we can perform, how we can make references to the existing value of col and the new value assigned to col.

FOLLOWUP

Based on the update information in the question, here's an example of a BEFORE UPDATE trigger that satisfies the specification.

This is for the UPDATE action. To get this same behavior with an INSERT statement, this trigger definition needs to be repeated, with BEFORE INSERT in place of BEFORE UPDATE.

(I would use the name _bu for the before update trigger, and _bi for before insert trigger.)

DELIMITER $$

CREATE TRIGGER  SafetyCertification_bu
BEFORE UPDATE ON   SafetyCertification
FOR EACH ROW
BEGIN
   -- set one of the `fusdateN` columns to current date
   -- which column to set depends on the value assigned to `fus` 
   IF NEW.fus = 'FUS1' THEN
      SET NEW.fusdate1 = DATE(NOW());
   ELSEIF NEW.fus = 'FUS2' THEN
      SET NEW.fusdate2 = DATE(NOW());
   ELSEIF NEW.fus = 'FUS3' THEN
      SET NEW.fusdate3 = DATE(NOW());
   END IF;
END$$

DELIMITER ;

second followup

To answer the question about using CASE WHEN statement in the context of a MySQL stored program...

We could implement the example trigger (directly above in this answer) replacing the IF-THEM with either of the two forms of the CASE statement.

either

   CASE 
      WHEN NEW.fus = 'FUS1' THEN
         SET NEW.fusdate1 = DATE(NOW());
      WHEN NEW.fus = 'FUS2' THEN
         SET NEW.fusdate2 = DATE(NOW());
      WHEN NEW.fus = 'FUS3' THEN
         SET NEW.fusdate3 = DATE(NOW());
   END CASE;

-or-

   CASE NEW.fus 
      WHEN 'FUS1' THEN
         SET NEW.fusdate1 = DATE(NOW());
      WHEN 'FUS2' THEN
         SET NEW.fusdate2 = DATE(NOW());
      WHEN 'FUS3' THEN
         SET NEW.fusdate3 = DATE(NOW());
   END CASE;

note

The CASE statement is available in MySQL stored programs; outside of a stored program, it is not a valid SQL statement.

Also, we should not confuse this CASE statement with the CASE expression. The CASE expression is valid in the context of a SQL statement, such as a SELECT or UPDATE statement.

DEMONSTRATION

"I examined the Trigger, But unfortunately does not save anything in fusDates."

@Christiano: here is a simple demonstration

create table

CREATE TABLE `safety_certification`
( id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
, fus       VARCHAR(5)
, fusdate1  DATE
, fusdate2  DATE
, fusdate3  DATE
) ENGINE=INNODB
;

populate table with demonstration rows

INSERT INTO `safety_certification` (id, fus, fusdate1, fusdate2, fusdate3) VALUES
( 1, '', NULL, NULL, NULL)
,( 2, '', NULL, NULL, NULL)
,( 3, '', NULL, NULL, NULL)
,( 4, '', NULL, NULL, NULL)
,( 5, '', NULL, NULL, NULL)
;

create trigger

DELIMITER $$

CREATE TRIGGER  `safety_certification_bu`
BEFORE UPDATE ON   `safety_certification`
FOR EACH ROW
BEGIN
   -- set one of the `fusdateN` columns to current date
   -- which column to set depends on the value assigned to `fus`
   IF NEW.fus = 'FUS1' THEN
      SET NEW.fusdate1 = DATE(NOW());
   ELSEIF NEW.fus = 'FUS2' THEN
      SET NEW.fusdate2 = DATE(NOW());
   ELSEIF NEW.fus = 'FUS3' THEN
      SET NEW.fusdate3 = DATE(NOW());
   END IF;
END$$

DELIMITER ;

updates will exercise BEFORE UPDATE trigger we just defined

UPDATE `safety_certification` sc SET sc.fus = 'FUS1' WHERE sc.id = 1 ;
UPDATE `safety_certification` sc SET sc.fus = 'FUS2' WHERE sc.id = 2 ;
UPDATE `safety_certification` sc SET sc.fus = 'FUS3' WHERE sc.id = 3 ;
UPDATE `safety_certification` sc SET sc.fus = 'FUS4' WHERE sc.id = 4 ;

display contents of table

SELECT * FROM `safety_certification`;

returns:

    id  fus     fusdate1    fusdate2    fusdate3
------  ------  ----------  ----------  ------------
     1  FUS1    2018-05-04  (NULL)      (NULL)
     2  FUS2    (NULL)      2018-05-04  (NULL)
     3  FUS3    (NULL)      (NULL)      2018-05-04
     4  FUS4    (NULL)      (NULL)      (NULL)
     5          (NULL)      (NULL)      (NULL)

seems like the trigger is populating the columns fusdate1, fusdate2 and fusdate3 per the specification, when some particular values are assigned to fus