Trigger after Insert value from another table

Question

I have two tables Employee and Employee Log, I created a trigger after insert, I have no problem with my after insert trigger using entities or values from employee, to insert directly to Log table, but how would I insert value from another table? like Admin_ID. I want a log table with transaction, datetime and who created.

Thank you.

  CREATE TRIGGER emp_log_af AFTER INSERT ON emp

  FOR EACH ROW

   BEGIN

     INSERT INTO emp_log (action,id,ts, ad_id)

     VALUES('create',NEW.id,NOW());

   END;

the ad_id or admin_id is my problem im using C# my employee table does not contain admin_id or ad_id — George of Jungle
Are you looking for CURRENT_USER? Or SELECT SUBSTRING_INDEX(USER(),'@',1) from dual Pulled from here — TaW

George of Jungle George of Jungle · Accepted Answer · 2014-07-16T06:41:19

its not possible with trigger what i did was get the max(id) to do that i created a stored procedure.

 DELIMITER $$

 CREATE PROCEDURE sp_insert_user_log
 (
    IN ia Varchar(12), 
    IN ie INT, 
    IN ix datetime  
 )

  BEGIN
   DECLARE id INT DEFAULT 0;
      SELECT MAX(user_id) INTO id FROM user ORDER BY user_id DESC LIMIT 1;
   BEGIN 
        INSERT INTO `user_log`(`action`, `user_id`, `employee_id`, `ts`) VALUES (ia, id, ie, ix);       
    END; 

   END$$


   DELIMITER ;

   DELIMITER $$

Trigger after Insert value from another table

1 Answers