CODEWITHSUNDEEP
mysqlsqldatabasedatabase-designtriggers
SixTwentyFour
SixTwentyFour

Reputation: 41

How to create a trigger that moves deletions to another table?

I have a MySQL database with a project and a projectover table (projects are completed by employees) and I need to create a trigger that whenever a project is deleted from the project table, it is placed in the projectover table. I was told the syntax for a trigger in MySQL is as follows: 

CREATE
  [DEFINER = {user| CURRENT_USER }]
  TRIGGER trigger_name trigger_time trigger_event
  ON tbl_name FOR EACH ROW trigger_stmt

Using that template I would assume the correct way to do this would be:

CREATE
  [DEFINER = {user| CURRENT_USER }]
  TRIGGER project_done trigger_time delete
  ON project FOR EACH ROW insert into projectover

Along those lines, I do not know the exact syntax for the action of inserting those records into the new table, and I do not know what the trigger_time means... any help would be appreciated.

Upvotes: 1

Views: 2787

Answers (1)

Ian Kenney
Ian Kenney

Reputation: 6436

This site has a nice example for you.

CREATE TRIGGER Employee_Trigger
AFTER delete ON employee
FOR EACH ROW
BEGIN
  insert into employee_log values(old.id,old.first_name,
   old.last_name,old.start_date,old.end_date,
   old.city,old.description,curtime());
 END

Update

Note that the insert statement can be easier to

Trigger docs here Insert docs here

Upvotes: 2

Related Questions