Trigger
Now that we have finished coding our stored procedures, let’s move on to triggers. We’ll only be coding one trigger - payment_check.
This trigger checks the outstanding balance of a member, which is recorded in the payment_due column of the members table.
If payment_due is more than $0 and the member terminates his/her account, we’ll transfer the data to the pending_terminations table. This table records all the termination requests that are pending due to an outstanding payment.
Let’s first declare the trigger as follows:
CREATE TRIGGER payment_check BEFORE DELETE ON members FOR EACH ROW
As you can see, this trigger is activated when we try to delete a record from the members table.
Next, between the BEGIN and END $$ markers, we need to do a few things:
First, we need to declare a local variable called v_payment_due. The data type of v_payment_due matches that of the payment_due...