Mysql – How to Add AFTER Delete Trigger in My case

dbmsMySQLmysql-workbenchtrigger

I am Making a Database Management System of Hospital in MySQL WorkBench, and i Want to Make a After Delete Trigger in my case. The scenario is i have two Tables

1) The Patients which are currently admitted in the Hospital, that Table is represented as resident

and

2) This is The Table where i keep Records of the Outgoing Patient (i.e the Patient who successfully Undergoes treatment and walks out of Hospital) as outpatient.

Now, in the Resident table i have a column called residentID which distinguishes the Patients who are there by Just there Unique Number, and In outpatient i keep the records by OutpatientID of which patient who left Hospital.

So I want to make a Trigger in Such a way that Whenever i Write a DELETE FROM resident where residentID = 1 than the Person's ID who just got Deleted from resident table comes into Outpatient Table. So how do we make Such Trigger which does this, I am pretty much quizzed here. This is what i want, any Inputs would be highly appreciated.

Best Answer

You should take the vbalues of OLD and use them in INSERT command.

Something like this:

CREATE TRIGGER triger_name_after_delete
AFTER DELETE
   ON resident FOR EACH ROW
BEGIN
   -- Insert record into Outpatient table
   INSERT INTO Outpatient (residentID, [rest columns names...] )
   VALUES ( OLD.residentID, OLD.[rest columns names...])
END;
Related Question