CODEWITHSUNDEEP
mysqltriggers
Onic Team
Onic Team

Reputation: 1658

How can I update MySQL table row's when 'BEFORE UPDATE' trigger is firing?

I created a table

Databases Name - mytrigger;
Table name - employee_audit

use mytrigger;
create table employee_audit(
     id int auto_increment primary key,
     employeeNumber int not null,
     lastName varchar(50) not null,
     changee datetime default null,
     action varchar(50) default null
    );

After that, I created one update trigger

My trigger name is

before_employees_update

DELIMITER $$
create trigger before_employees_update
before update on employee_audit
for each row
begin
insert into employee_audit
set action ='update',
employeeNumber = OLD.employeeNumber,
lastName = OLD.lastName,
changee = now();
end$$
DELIMITER ;

After that, I inserted values in table using this command ->

insert into employee_audit values(1,112,'prakash','2015-11-12 15:36:20' ,' ');

After that, I want to update my table row where id =1

update employee_audit set lastName = 'Sharma' where employeeNumber =112;

But it is not executed give an error

ERROR 1442 (HY000): Can't update table 'employee_audit' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

When I searched on Google I found a lot of Question with the same error. But not able to fix my problem. what is the reason I'm not able to update my row?

Upvotes: 0

Views: 250

Answers (2)

Sagar Gangwal
Sagar Gangwal

Reputation: 7957

What i suggest,you can create one log table like employee_audit_LOG .

And on every insert or update in main table you can make new entry in this table or update existing record.

Also you can add updated_timestamp column to that LOG table which maintain when did specific record get updated.

Upvotes: 1

Mit Mehta
Mit Mehta

Reputation: 212

The error itself tells you the answer. This is because, you can't use the same table on which trigger is being executed. You need to store your audit logs into some different table.

Upvotes: 1

Related Questions