CODEWITHSUNDEEP
mysqltriggers
Hoch
Hoch

Reputation: 518

How to update a table using trigger mysql

I have two tables, t1 and t2. I'm trying to make a trigger so that when t1 gets an update, I check if what was updated was t1.nStatus.

If t1.nStatus = 2, I need to set t2.bEnabled = 1 for all t2.customer_Id that are equal to t1.nId

CREATE OR REPLACE TRIGGER change AFTER UPDATE ON t1
    FOR EACH ROW
BEGIN
    IF NEW.nStatus = 2 THEN
        UPDATE t2 SET bEnabled = 1 WHERE t2.immobile_id = NEW.nId;
    END IF;
END;

The error I got:

1 queries executed, 0 success, 1 errors, 0 warnings

Query: CREATE OR REPLACE trigger changeStatusImmobile after UPDATE on immobile for each row begin IF NEW.nStatus = 2 then UPDATE select...

Error Code: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 5

Execution Time : 0 sec Transfer Time : 0 sec Total Time : 0.146 sec

Have you some idea?

Upvotes: 0

Views: 71

Answers (1)

Vadim Sirbu
Vadim Sirbu

Reputation: 681

Try setting a delimiter

DELIMITER $$

CREATE OR REPLACE TRIGGER change AFTER UPDATE ON t1
    FOR EACH ROW
BEGIN
    IF NEW.nStatus = 2 THEN
        UPDATE t2 SET bEnabled = 1 WHERE t2.immobile_id = NEW.nId;
    END IF;
END$$

DELIMITER ;

Upvotes: 0

Related Questions