CODEWITHSUNDEEP
mysqltriggers
Josh Ortega
Josh Ortega

Reputation: 179

How to use an IF statement in a MYSQL Trigger?

Trying to create a trigger that will look for two conditions and update an audit table with those. The trigger works on a customers table and looks for an update to phone number and/or credit limit. The audit table will have a column that show's which field was update or if both were updated.

Here's what I have so far (update with code from below) same error:

#Here's the audit table I created:

create table cust_audit(customerNumber INT, customerName varchar(50), 
                        phone varchar(50), creditLimit dec(10,2), 
                        last_update timestamp, `user` varchar(30), 
                        row_value varchar(20), fields_changed,   
                        varchar(30));


Delimiter $$

CREATE TRIGGER cust_upd_old
AFTER UPDATE 
ON customers
FOR EACH ROW
Begin
INSERT INTO audit_table(customerNumber, 
                        customerName,
                        phone, 
                        creditLimit, 
                        last_update, 
                        `user`, 
                        row_value, 
                        fields_changed)
VALUES (OLD.customerNumber, 
        OLD.customerName,
        OLD.phone, 
        OLD.creditLimit, 
        now(), 
        current_user(), 
        'before update', 
        CASE WHEN NEW.phone = OLD.phone THEN 'creditLimit'
             WHEN NEW.creditLimit = OLD.creditLimit THEN 'phone number'
             ELSE 'both'END);
END $$
DELIMITER ;

The trigger will run but when I try to run an update statement the update fails with code Error Code: 1054. Unknown column 'customerNumber' in 'field list' but the select statement will work so it's some error with the trigger I think. USING MYSQL 8.0.21

update customers set phone = '2128822470' where customerNumber = 181;

select * from customers where customerNumber = 181;

Upvotes: 0

Views: 35

Answers (1)

Akina
Akina

Reputation: 42632

Can be simplified to

CREATE TRIGGER cust_upd_old
AFTER UPDATE 
ON customers
FOR EACH ROW
INSERT INTO audit_table(customerNumber, 
                        customerName,
                        phone, 
                        creditLimit, 
                        last_update, 
                        `user`, 
                        row_value, 
                        fields_changed)
VALUES (OLD.customerNumber, 
        OLD.customerName,
        OLD.phone, 
        OLD.creditLimit, 
        now(), 
        current_user(), 
        'before update', 
        CASE WHEN NEW.phone = OLD.phone THEN 'creditLimit'
             WHEN NEW.creditLimit = OLD.creditLimit 'phone number'
             ELSE 'both' END);

Upvotes: 1

Related Questions