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...