marrowyung
asked on
MariaDB trigger doesn't work as expected.
hi,
I am studying MariaDB trigger:
this is a trigger:
it will call a SP called ADD_JOB_HISTORY and what this SP does is:
when when I insert a record in EMPLOYEES tables nothing created in JOB_HISTORY table, what is going on ?
I am studying MariaDB trigger:
this is a trigger:
CREATE DEFINER=`root`@`%` TRIGGER `HR`.`UPDATE_JOB_HISTORY` AFTER UPDATE ON HR.EMPLOYEES FOR EACH ROW
Begin
IF (NEW.job_id <> OLD.job_id) AND(NEW.department_id <> OLD.department_id) then
CALL HR.ADD_JOB_HISTORY(OLD.EMPLOYEE_ID,OLD.HIRE_DATE,CURRENT_TIMESTAMP,OLD.JOB_ID,OLD.DEPARTMENT_ID);
end if;
end; it will call a SP called ADD_JOB_HISTORY and what this SP does is:
DROP PROCEDURE IF EXISTS HR.ADD_JOB_HISTORY;
CREATE PROCEDURE HR.`ADD_JOB_HISTORY`(P_EMP_ID MEDIUMINT
, P_START_DATE DATETIME
, P_END_DATE DATETIME
, P_JOB_ID VARCHAR(10)
, P_DEPARTMENT_ID SMALLINT)
BEGIN
INSERT INTO JOB_HISTORY(EMPLOYEE_ID, START_DATE, END_DATE,
JOB_ID, DEPARTMENT_ID)
VALUES(P_EMP_ID, P_START_DATE, P_END_DATE, P_JOB_ID, P_DEPARTMENT_ID);
END; when when I insert a record in EMPLOYEES tables nothing created in JOB_HISTORY table, what is going on ?
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
johnsone
I cannot say for sure that it should be AFTER INSERT. It may need to be something different, I would assume that it would be more than just INSERT.
ASKER
I think I need to give you more information on how I get it, it is the Ispirer conversion tools that convert a SINGLE oracle trigger to 3 x trigger and it is ONE of them.
other one is AFTER INSERT , and AFTER DELETE. So I have all of them , I am testing one by one but that one already doen't work.
someone told me that it is the "UPDATE ON" should not have anything to happen and I see the key word should be this "
AFTER UPDATE".
why it doesn't work .
other one is AFTER INSERT , and AFTER DELETE. So I have all of them , I am testing one by one but that one already doen't work.
someone told me that it is the "UPDATE ON" should not have anything to happen and I see the key word should be this "
AFTER UPDATE".
why it doesn't work .
I think why it doesn't work has been covered. An update trigger doesn't fire on insert. I'm not sure what part of that you aren't getting.
ASKER
ok, this mean AFTER UPDATE will not work but AFTER INSERT, this is what the point is and this should be what I should test.
ok now direction is more clear but one thing. what is the AFTER UPDATE designed for then.. ?
ok now direction is more clear but one thing. what is the AFTER UPDATE designed for then.. ?
Seems pretty self explanatory. AFTER UPDATE would do something after an update.
ASKER
yes, so you mean one only work when I update EXISTING record, not insert a record, right?
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
actually I program trigger myself on MySQL and what my test result give me is a bit surprise ! I will test AFTER INSERT but not UPDATE today.
I just want to make it a very simple test for the team.
I just want to make it a very simple test for the team.
ASKER
tks all.
You're welcome!
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
so create all AFTER and BEFORE trigger?
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
excellent and tks. I never use a replace trigger.
ASKER
"Not a MySQL expert, but REPLACE is a statement. According to the documentation for triggers, you cannot have a trigger on a REPLACE."
seems both doc do not say clearly about:
"Also note that replace statements trigger delete and insert triggers. Not update."
it said:
"REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted. See Section 13.2.6, “INSERT Syntax”."
"o use REPLACE, you must have both the INSERT and DELETE privileges for the table."
which means it will do insert and delete.
seems both doc do not say clearly about:
"Also note that replace statements trigger delete and insert triggers. Not update."
it said:
"REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted. See Section 13.2.6, “INSERT Syntax”."
"o use REPLACE, you must have both the INSERT and DELETE privileges for the table."
which means it will do insert and delete.
What is the difference between this:
and this:
Other than the documentation uses more words to say the same thing.
Also note that replace statements trigger delete and insert triggers. Not update.
and this:
REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.
Other than the documentation uses more words to say the same thing.
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
skullnobrains,
"but the statement is atomic so the deletion is not performed if the insert fails either because of a duplicate key or a trigger"
but it is a delete first, not insert failed first then deletion also fail right?
"but the statement is atomic so the deletion is not performed if the insert fails either because of a duplicate key or a trigger"
but it is a delete first, not insert failed first then deletion also fail right?
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
"there is no REPLACE trigger and if you expect something to happen when a REPLACE is issued, the action would need to be in the DELETE or INSERT trigger as the UPDATE trigger would not fire. That is the point that is trying to be made."
this means can't do any trigger with replace statment, right ?
this means can't do any trigger with replace statment, right ?
ASKER
this question already closed.
think of the delete and insert as part of the same transaction.
in real life, they are mixed but that has no incidence on what you experiment as a user
if the insertion cannot be performed for some reason or the BEFORE insert trigger fails, the deletion is not performed either
the real algo is something along these lines
- BEGIN
- check unique keys. if the insert cannot succeed, reject the statement
- lock rows for writing. wait endlessly for the lock
- execute BEFORE DELETE triggers. FAIL if a trigger fails
- execute DELETE
- execute AFTER DELETE triggers
- execute BEFORE INSERT triggers. ROLLBACK if a trigger fails
- execute INSERT
- execute AFTER INSERT triggers
- COMMIT
in real life, they are mixed but that has no incidence on what you experiment as a user
if the insertion cannot be performed for some reason or the BEFORE insert trigger fails, the deletion is not performed either
the real algo is something along these lines
- BEGIN
- check unique keys. if the insert cannot succeed, reject the statement
- lock rows for writing. wait endlessly for the lock
- execute BEFORE DELETE triggers. FAIL if a trigger fails
- execute DELETE
- execute AFTER DELETE triggers
- execute BEFORE INSERT triggers. ROLLBACK if a trigger fails
- execute INSERT
- execute AFTER INSERT triggers
- COMMIT
ASKER
"if the insertion cannot be performed for some reason or the BEFORE insert trigger fails, the deletion is not performed either"
normal and make sense.
but you are meaning that insert operation will begin first then delete? for me, it is delete first and then insert..
"execute BEFORE DELETE triggers. FAIL if a trigger fails
- execute DELETE
- execute AFTER DELETE triggers
- execute BEFORE INSERT triggers. ROLLBACK if a trigger fails
- execute INSERT
- execute AFTER INSERT triggers
- COMMIT"
but this one tells it is delete first and then insert.
"- BEGIN
- check unique keys. if the insert cannot succeed, reject the statement"
with this one:
"- BEGIN
- check unique keys. if the insert cannot succeed, reject the statement"
insert twice ?
normal and make sense.
but you are meaning that insert operation will begin first then delete? for me, it is delete first and then insert..
"execute BEFORE DELETE triggers. FAIL if a trigger fails
- execute DELETE
- execute AFTER DELETE triggers
- execute BEFORE INSERT triggers. ROLLBACK if a trigger fails
- execute INSERT
- execute AFTER INSERT triggers
- COMMIT"
but this one tells it is delete first and then insert.
"- BEGIN
- check unique keys. if the insert cannot succeed, reject the statement"
with this one:
"- BEGIN
- check unique keys. if the insert cannot succeed, reject the statement"
insert twice ?
The algo is ptetty much the one i wrote... and i believe you are geting it
just figure out that checking for a possibke failure condition ( such as unique keys or locking ) does not mean the insert is performed at that stage.
Regarding late failure conditions ( after the delete ), the transaction is rollbacked, so the deleted row is undeleted
just figure out that checking for a possibke failure condition ( such as unique keys or locking ) does not mean the insert is performed at that stage.
Regarding late failure conditions ( after the delete ), the transaction is rollbacked, so the deleted row is undeleted
ASKER
"just figure out that checking for a possibke failure condition ( such as unique keys or locking ) does not mean the insert is performed at that stage.
"
so the order actually can be diff or very diff ? or just because of sth like this : such as unique keys or locking, so the order can be diff ?
" after the delete "
AFTER delete trigger .. ?
"the transaction is rollbacked, so the deleted row is undeleted"
actaully the replace operation should be ATOMIC ? so both delete and insert will be rollback ?
"
so the order actually can be diff or very diff ? or just because of sth like this : such as unique keys or locking, so the order can be diff ?
" after the delete "
AFTER delete trigger .. ?
"the transaction is rollbacked, so the deleted row is undeleted"
actaully the replace operation should be ATOMIC ? so both delete and insert will be rollback ?