Is it possible to create a trigger to copy values into same field in another row (in the same table)?

mysql, triggers

Solution

Are you looking for something like this?

CREATE TRIGGER tg_projects_insert
BEFORE INSERT ON projects
FOR EACH ROW
  SET NEW.date_code = IF(NULLIF(TRIM(NEW.date_code), '') IS NULL,
  (
    SELECT date_code 
      FROM projects
     WHERE project_code = NEW.project_code 
       AND item_code = NEW.item_code
     LIMIT 1
  ), NEW.date_code);

CREATE TRIGGER tg_projects_update
BEFORE UPDATE ON projects
FOR EACH ROW
  SET NEW.date_code = IF(NULLIF(TRIM(NEW.date_code), '') IS NULL,
  (
    SELECT date_code 
      FROM projects
     WHERE project_code = NEW.project_code 
       AND item_code = NEW.item_code
     LIMIT 1
  ), NEW.date_code);

Now we can insert and update row into projects table

-- insert a new row and let the trigger to set date_code automatically
INSERT INTO projects VALUES (5, 'project1', 'item1', NULL);

-- update all rows that currently have NULL in date_code 
-- and let the trigger to set date_code automatically
UPDATE projects SET date_code = NULL WHERE date_code IS NULL;

-- insert a new row and explicitly set a value to date_code
INSERT INTO projects VALUES (6, 'project2', 'item1', '1115');

Here is SQLFiddle demo

Problem

I have the following table `test` with the fields ``` id (PK), Project Code (CFK), item code (CFK) date_code ``` rows could look like: ``` id, project code, item code, date code 1, project1, item1, 1220 2, project1, item2, 1224 3, project1, item1, null 3, project1, item4, null 4, project1, item1, null ``` To update date code I can do that with a simple update table ``` update table test set date_code='1220' where project_code='project1' and item_code='item1' ``` But can this be done with a trigger on insert and update? I want the trigger find the same/matching composite foreign key fields (project code & item code) like in update table syntax and copy the same value in other rows for the date code field? All the rows would exist before the date_code field is given values so it is something that is done last.

Original source