update/insert triggers pass row to procedure call
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
Subject
Views
Written By
Posted
update/insert triggers pass row to procedure call
3213
May 30, 2017 05:50PM
1200
May 30, 2017 06:29PM
1086
May 30, 2017 06:34PM
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.