Can a trigger for a table can be applied for tracking in same table?

Can a trigger for a table can be applied for tracking in same table?

That is IF I have table Say "employee" Now can i create a triggers such that it tracks the change in the same "employee" table

trigger such as

create trigger "<triggers_name>"  before insert on employee
For each row
Begin
 insert int开发者_C百科o employee field = timestamp;
end

In short, no.

So let's think about this.

A "before" trigger will execute before the action (in this case, an "insert" action) takes place.

If this were to work, and you attempted to do an insert into the employee table every time an insert took place on the employee table, you'd loop forever in a big recursive mess.

Referenced from the MySQL docs

"Within a stored function or trigger, it is not permitted to modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger."

See this page for more info.

That said, if you need to do tracking when inserts take place, I would recommend using a second table which can certainly be referenced via a trigger. You and create a FK to the employee table and put your timestamp column in that new table and do something like this:

for each row
insert into employee_tracking (id, timestamp) on duplicate key update set timestamp = new_timestamp;

If you just wanted to track the insert time, you can have your datefield default to NOW().

I wonder if you can do an update on a table that was just inserted? The recursion I understand with the second insert but maybe you can update the same table?