• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 203
  • Last Modified:

Create Trigger with multiple Case Statments

I'm trying to create a trigger to track what values were modified, by whom, and what time
they updated the record(s).

I'm wanting to create a trigger with multiple case statements, but can't seem to get it to work.
Thoughts?

USE `file_room`;
CREATE 
DEFINER=CURRENT_USER

TRIGGER comment_a_update AFTER UPDATE ON fileroom
	FOR EACH ROW BEGIN
		CASE WHEN OLD.PREP_BY <> NEW.PREP_BY 
        THEN
            INSERT INTO DELTA (table_name, field_name, date_of, user_name, old_val, new_val) VALUES ('fileroom', 'prep_by',now(), user(),old.prep_by, new.prep_by)
    END
        
		CASE WHEN OLD.PREP_DATE <> NEW.PREP_DATE 
        THEN
            INSERT INTO DELTA (table_name, field_name, date_of, user_name, old_val, new_val) VALUES ('fileroom', 'prep_date',now(), user(),old.prep_by, new.prep_by)
    END
	END;

Open in new window

0
JustinW
Asked:
JustinW
1 Solution
 
johanntagleCommented:
If you just have one condition for a column then just use IF instead of CASE:

USE `file_room`;
DELIMITER |
CREATE TRIGGER comment_a_update AFTER UPDATE ON fileroom
FOR EACH ROW BEGIN
    IF OLD.PREP_BY <> NEW.PREP_BY 
      THEN
         INSERT INTO DELTA (table_name, field_name, date_of, user_name, old_val, new_val) VALUES ('fileroom', 'prep_by',now(), user(),old.prep_by, new.prep_by);
    END IF;
        
    IF OLD.PREP_DATE <> NEW.PREP_DATE 
     THEN
       INSERT INTO DELTA (table_name, field_name, date_of, user_name, old_val, new_val) VALUES ('fileroom', 'prep_date',now(), user(),old.prep_by, new.prep_by);
    END IF;
END;
|
DELIMITER ;

Open in new window


I also added missing semicolons to your code.  Also refer to the following:
http://dev.mysql.com/doc/refman/5.1/en/flow-control-constructs.html
http://dev.mysql.com/doc/refman/5.1/en/triggers.html
0
 
JustinWAuthor Commented:
Absolutely what I was looking for!
Thank you!
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now