CODEWITHSUNDEEP
mysqltriggers
Batman101
Batman101

Reputation: 13

Create Trigger in MySQL and Update

I need help creating a particular trigger using MySQL. The details for my trigger is given below along with my current statement. However, my statement keeps giving me an error message so I'm doing something wrong. Any assistance would be great! Thanks!

Problem a) Create a trigger called membership_balance_updates that will capture any updates made to the mem_balance column in membership. The trigger should only capture those transactions in which the member’s balance actually changes. The mem_num, old mem_balance, new mem_balance, user, transaction date should be placed into a membership_balance_audit table.

My Statement: 

CREATE TRIGGER membership_balance_updates
AFTER UPDATE OF mem_balance ON membership
FOR EACH ROW
INSERT INTO membership_balance_audit 
VALUES (mem_mum, old_mem_balance, new_mem_balance, transaction_date, 
transaction_user);

Table "Membership"

Here's 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));

Part c

Upvotes: 0

Views: 462

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521178

Try using this:

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;

Upvotes: 1

Related Questions