Grant privileges to a certain user
GRANT ALL PRIVILEGES ON *.* TO someone@localhost IDENTIFIED BY ‘theirpassword’ WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO someone@”%” IDENTIFIED BY ‘theirpassword’ WITH GRANT OPTION;
The second statement is for granting privileges to the account when not on the local server (not localhost). Both statements will give all privileges on all tables and databases.
Grant only trigger privilege
GRANT Trigger ON *.* TO someone@localhost IDENTIFIED BY ‘theirpassword’ WITH GRANT OPTION;
Show all triggers
SHOW TRIGGERS;
Create a trigger
CREATE TRIGGER MyTrigger BEFORE INSERT ON employee
FOR EACH ROW
BEGIN
IF NEW.JobTitle = ‘Manager’ THEN
SET NEW.Wage = 30000;
END IF;
END;
The NEW keyword above references the row which is going to be inserted. Therefore just before the insert is done, if the JobTitle was ‘Manager’, the Wage column would be updated to 30000 before being committed to the database.