The 365 Data Science team is proud to invite you to our own community forum. A very well built system to support your queries, questions and give the chance to show your knowledge and help others in their path of becoming Data Science specialists.
Ask
Anybody can ask a question
Answer
Anybody can answer
Vote
The best answers are voted up and moderated by our team

COMMIT doesn’t work

COMMIT doesn’t work

Super Learner
0
Votes
1
Answer

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’;

1 Answer

365 Team
0
Votes

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

×
Complete Data Science Training
Save 60%