CODEWITHSUNDEEP
phpmysql
Meena
Meena

Reputation: 967

How to create a trigger for delete php myadmin

I have two tables A and B. If I remove a entry in the table A, I want to update the status of the entry in the table B using the user id. I'm using this code:

CREATE TRIGGER blockuserundo BEFORE DELETE ON user_blocked
FOR EACH ROW BEGIN
UPDATE members SET ty_status = '1' WHERE user_id = OLD.block_user_id ;
END;

It shows an error like:

no new row in the trigger delete

Please guide me to write the query for the above scenario.

Upvotes: 1

Views: 791

Answers (1)

The Scrum Meister
The Scrum Meister

Reputation: 30111

You need to set the delimiter to something other then ;

DELIMITER |
CREATE TRIGGER blockuserundo BEFORE DELETE ON user_blocked
FOR EACH ROW BEGIN
UPDATE members SET ty_status = '1' WHERE user_id = OLD.block_user_id ;
END;
|
DELIMITER ;

Code below works as expected:

DROP TABLE IF EXISTS user_blocked;
CREATE TABLE user_blocked 
(
    block_user_id INT
);
DROP TABLE IF EXISTS members;
CREATE TABLE members 
(
    user_id INT,
    ty_status CHAR(1)
);

INSERT user_blocked
VALUES (1),(2),(3),(4),(5);

INSERT members
VALUES (1, '0'),(2, '0'),(3, '0'),(8, '0'),(9, '1');

DELIMITER |
CREATE TRIGGER blockuserundo BEFORE DELETE ON user_blocked
FOR EACH ROW BEGIN
UPDATE members SET ty_status = '1' WHERE user_id = OLD.block_user_id ;
END;
|
DELIMITER ;

SELECT * FROM members;

DELETE FROM user_blocked WHERE block_user_id IN(1,2);
SELECT * FROM members;

Upvotes: 2

Related Questions