MariaDB trigger doesn't work as expected.

marrowyung
marrowyung used Ask the Experts™
on
hi,

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; 

Open in new window


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; 

Open in new window


when when I insert a record in EMPLOYEES  tables nothing created in JOB_HISTORY table, what is going on ?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior Oracle DBA
Commented:
Why do you think it would run on insert?

CREATE DEFINER=`root`@`%` TRIGGER `HR`.`UPDATE_JOB_HISTORY` AFTER UPDATE ON HR.EMPLOYEES FOR EACH ROW
Fractional CTO
Distinguished Expert 2018
Commented:
What johnsone is suggested is change AFTER UPDATE to AFTER INSERT.
johnsoneSenior Oracle DBA

Commented:
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.
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

marrowyungSenior Technical architecture (Data)

Author

Commented:
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 .
johnsoneSenior Oracle DBA

Commented:
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.
marrowyungSenior Technical architecture (Data)

Author

Commented:
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.. ?
johnsoneSenior Oracle DBA

Commented:
Seems pretty self explanatory.  AFTER UPDATE would do something after an update.
marrowyungSenior Technical architecture (Data)

Author

Commented:
yes, so you mean one only work when I update EXISTING record, not insert a record, right?
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
Not sure how else to explain this differently than others already have.

Triggers ONLY fire based on the directives you told it to fire on.  If you create an UPDATE trigger, it will only fire on an UPDATE statement against the tables/columns you told it to fire on.

An INSERT trigger will only fire on INSERT statements on the table you told it to fire on.

Triggers are pretty much the same on every database product that allows them.  SQL Server/Oracle/Postgres/???  They behave pretty much the same way.
marrowyungSenior Technical architecture (Data)

Author

Commented:
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.
marrowyungSenior Technical architecture (Data)

Author

Commented:
tks all.
David FavorFractional CTO
Distinguished Expert 2018

Commented:
You're welcome!
In ost cases, rule of thumb is to create both triggers identically
marrowyungSenior Technical architecture (Data)

Author

Commented:
so create all AFTER and BEFORE trigger?
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
You create the trigger you need to meet your requirements.  We cannot tell you what you need to create.

Some times you might need to access the data BEFORE it is inserted/updated.  Some times AFTER.

A made up example:  You get commission on every sale based on a scale of your months sales and every sale is logged.  You make a sale and a BEFORE trigger will calculate your commission on that sale and an AFTER trigger will log/audit you made the sale.
Both insert and update trigger.

Regarding after or before, rule of thumb is use before triggers in most cases and use after if your insertion produces an id which you need in the trigger. They are otherwise functionally very similar.
Also note that replace statements trigger delete and insert triggers. Not update.
marrowyungSenior Technical architecture (Data)

Author

Commented:
excellent and tks. I never use a replace trigger.
johnsoneSenior Oracle DBA

Commented:
Not a MySQL expert, but REPLACE is a statement.  According to the documentation for triggers, you cannot have a trigger on a REPLACE.  I believe if you read the documentation on the REPLACE statement, you can see why.

As this question is closed, any further discussion should be in a new question.
marrowyungSenior Technical architecture (Data)

Author

Commented:
"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.
johnsoneSenior Oracle DBA

Commented:
What is the difference between this:

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.
which means it will do insert and delete

yes. actually delete first and then insert.

but the statement is atomic so the deletion is not performed if the insert fails either because of a duplicate key or a trigger

there is no dedicated replace trigger but the statement is available from within the trigger so it is feasible to emulate. i don't see the point, though.
marrowyungSenior Technical architecture (Data)

Author

Commented:
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?
johnsoneSenior Oracle DBA
Commented:
Regardless, 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.

Again, this question is closed, so I'm not commenting anymore.
marrowyungSenior Technical architecture (Data)

Author

Commented:
"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 ?
marrowyungSenior Technical architecture (Data)

Author

Commented:
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
marrowyungSenior Technical architecture (Data)

Author

Commented:
"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 ?
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
marrowyungSenior Technical architecture (Data)

Author

Commented:
"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  ?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial