MariaDB trigger doesn't work as expected.

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 ?
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johnsoneSenior Oracle DBACommented:
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
David FavorLinux/LXD/WordPress/Hosting SavantCommented:
What johnsone is suggested is change AFTER UPDATE to AFTER INSERT.
johnsoneSenior Oracle DBACommented:
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.
 Acronis Global Cyber Summit 2019 in Miami

The Acronis Global Cyber Summit 2019 will be held at the Fontainebleau Miami Beach Resort on October 13–16, 2019, and it promises to be the must-attend event for IT infrastructure managers, CIOs, service providers, value-added resellers, ISVs, and developers.

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 DBACommented:
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 DBACommented:
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?
slightwv (䄆 Netminder) 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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.