Create Trigger in MySQL and Update

Question

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

We need the column names for the membership and membership_balance_audit tables. — Tim Biegeleisen
– Tim Biegeleisen, Commented Dec 5, 2015 at 6:40
I replaced the last photo with part c (the final part of the problem). Looking at the data in part c, it appears to be some kind of root access information. I don't know where that could come from. I wonder if my teacher made a mistake. — Batman101
– Batman101, Commented Dec 5, 2015 at 7:43

Tim Biegeleisen · Accepted Answer · 2015-12-05 07:36:35Z

1

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;

edited Dec 5, 2015 at 7:36

answered Dec 5, 2015 at 6:38

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

Drew Over a year ago

do the if changed thing

Tim Biegeleisen Over a year ago

1 minute...also we don't really know the column name.z

Drew Over a year ago

ha you goofy guy, but that didn't stop you :P Off on other cleaning tasks, you got it covered

Batman101 Over a year ago

Tim, I tried your statement and it gave me an error message that says: Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF NEW.mem_balance <> OLD.mem_balance THEN SET mem_num = OLD.mem_num, ' at line 6

Tim Biegeleisen Over a year ago

Try again. Your original guess was way off.

|

Collectives™ on Stack Overflow

Create Trigger in MySQL and Update

1 Answer 1

12 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

12 Comments

Your Answer

Sign up or log in

Post as a guest

Related