MySQL Forums
Forum List  »  Triggers

update/insert triggers pass row to procedure call
Posted by: Wayne Rasmussen
Date: May 30, 2017 05:50PM

Suppose we have two triggers and a procedure as shown:

CREATE TRIGGER before_employee_update
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
CALL add_employees_table_changes_to_employees_audit_table(OLD.employeeNumber, OLD.lastName, "update");
END$$


CREATE TRIGGER before_employee_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
CALL add_employees_table_changes_to_employees_audit_table(NEW.employeeNumber, NEW.lastName, "insert");
END$$

CREATE PROCEDURE add_employees_table_changes_to_employees_audit_table(IN inemployeeNumber int, IN inlastName varchar(50), IN inaction varchar(50))
BEGIN
INSERT INTO employees_audit
SET action = inaction,
employeeNumber = inemployeeNumber,
lastname = inlastName,
changedat = NOW();
END $$

Is passing in to the call the values that need to be added to the employees_audit table the standard way to handle this call? Can the entire row be passed in or would that be bad?

Also, now this might be really old information or be information, but can this type of call from a trigger cause table locking issues?

Thank you in advance

Options: ReplyQuote


Subject
Views
Written By
Posted
update/insert triggers pass row to procedure call
1009
May 30, 2017 05:50PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.