0

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.

1

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; 
-2

Maybe you can create DELETE after INSERT. So, if like you case, you can use this trigger.

CREATE TRIGGER `delete_after_insert`
  AFTER INSERT
  ON  `outpatient`
  FOR EACH ROW 
  BEGIN
  DELETE FROM `resident` WHERE `residentID` = ''  
END;

You don't need old values for insert to outpatient. because you insert first and get the id for delete on resident table.

  • Placing the trigger on outpatient doesn't make sense; you would be automatically discharging every outpatient including the ones that were never admitted as inpatients. As for "You don't need old values for insert to outpatient. because you insert first and get the id for delete on resident table.", well, you do need the information, and it is already in OLD, and the alternative is to key it all over again, so... – Darwin von Corax May 10 '16 at 1:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.