gymcode gymcode - 1 year ago 114
MySQL Question

Delimiter and Trigger Inquiry

May I know what the following MySQL trigger does?

CREATE TRIGGER before_results_update
BEFORE UPDATE ON Student_Results
INSERT INTO Result_Changes
SET ACTION = 'update',
ResultID = OLD.ID,
Result = OLD.Result,
User = USER(),
ChangedOn = NOW();
END ??

Answer Source

DELIMITER creates a demarcation of the whole block. It uses ?? as the string if you will for the whole wrapper at the beginning and end. At the end, ; is set as the DELIMITER that we are all used to typing for end-of-statement. DELIMITERS are not used in SQLFiddle or PHPMyAdmin, but they are on MySQL Workbench and others. So it is a client-side thing. Depends what you are using.

Before an actual UPDATE to table Student_Results it performs an INSERT (using a not-very used style of an insert stmt) to table Result_Changes specifying 5 column names.

The UPDATE has not technically occurred yet in Student_Results. But the trigger has access to the rows about to be updated there and they are referenced with the special "row name" called OLD.

The FOR EACH ROW could loop for 1 to N times.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download