a) Create a trigger called membership_balance_updates that will capture any upda

Question

a) Create a trigger called membership_balance_updates that will capture any updates made to the balance column in membership. The trigger should only capture those transactions in which the member’s balance actually changes. The membership_id, old balance, new balance, user, transaction date should be placed into a membership_balance_audit table. Use the following script to create the membership_balance_audit table. CREATE TABLE IF NOT EXISTS membership_balance_audit (mem_num INTEGER, old_mem_balance DECIMAL(10,2), new_mem_balance DECIMAL(10,2), transaction_date TIMESTAMP, transaction_user VARCHAR(50));

Explanation / Answer

CREATE TRIGGER membership_balance_updates AFTER UPDATE ON membership FOR EACH ROW BEGIN IF NEW.mem_balance OLD.mem_balance THEN INSERT INTO membership_balance_audit (mem_mum, old_mem_balance, new_mem_balance, transaction_date, transaction_user) VALUES (OLD.membership_id, OLD.mem_balance, NEW.mem_balance, NOW(), CONCAT(OLD.first_name, ' ', OLD.last_name)); END IF; END;

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

---