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 on membership_balance_updates
after update membership // table_name
for each row
begin // loop
if neww_1.mem_balance <> oldd_1.mem_balance // oldd1 and neww1 are objects
insert into membership_balance_audit
(mem_num, oldd1_mem_balance, neww1_mem_balance, transaction_date, transaction_user)
values(oldd1.membership_id, oldd1.mem_balance, neww1.mem_balance,
concat(oldd1.name_first, ' ', oldd1.name_last)); // concat means to join two strings
end;
end;

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.