COMMIT doesn't work
Hi there,
I am reviewing the Triggers lesson and again, found out that COMMIT seems to be not working on my workbench. Again, I am using a MacOS 10.15.4 and Workbench 8.0.19.
After the Trigger lecture sample exercise and I tried ROLLBACK but the inserted new row in salaries table with emp_no 10001 with "0" salary is still there.
Is there something wrong with the software?
Thanks, Yunfeng
-- LECTURE: MySQL Triggers
USE employees;
COMMIT;
-- BEFORE INSERT
DELIMITER $$ CREATE TRIGGER before_salaries_insert
BEFORE INSERT ON salaries
FOR EACH ROW
BEGIN
IF NEW.salary < 0 THEN
SET NEW.salary = 0;
END IF;
END $$ DELIMITER ;
SELECT
*
FROM
salaries; SELECT
*
FROM
salaries
WHERE
emp_no = '10001';
INSERT INTO salaries VALUES('10001', -92891, '2010-06-22', '9999-01-01'); SELECT
*
FROM
salaries
WHERE
emp_no = '10001';
USE employees;
COMMIT;
-- BEFORE INSERT
DELIMITER $$ CREATE TRIGGER before_salaries_insert
BEFORE INSERT ON salaries
FOR EACH ROW
BEGIN
IF NEW.salary < 0 THEN
SET NEW.salary = 0;
END IF;
END $$ DELIMITER ;
SELECT
*
FROM
salaries; SELECT
*
FROM
salaries
WHERE
emp_no = '10001';
INSERT INTO salaries VALUES('10001', -92891, '2010-06-22', '9999-01-01'); SELECT
*
FROM
salaries
WHERE
emp_no = '10001';
1 answers ( 0 marked as helpful)
Hi Yunfeng!
Thanks for reaching out.
In this case, we are talking about statements that create an implicit commit. Triggers are on the list.
In other words, the execution of certain statements (and hence, their queries), affect the state of the database directly, so to speak, even without explicitly executing a COMMIT; statement afterwards. You can consult the following page from the documentation for more information. Thank you. https://dev.mysql.com/doc/refman/8.0/en/implicit-commit.html Hope this helps.
Best,
Martin
In other words, the execution of certain statements (and hence, their queries), affect the state of the database directly, so to speak, even without explicitly executing a COMMIT; statement afterwards. You can consult the following page from the documentation for more information. Thank you. https://dev.mysql.com/doc/refman/8.0/en/implicit-commit.html Hope this helps.
Best,
Martin