phpmyadmin Question posted in
The official documentation can be found here.

Delete Rows from one table when specific columns are updated on other linked table – SQL Trigger – Phpmyadmin

I have a table named Seller and another table api_tokens.
I want to remove all rows from api_token table of the seller when specific column email/password is updated from Seller.
sellerId is the foreign_key in tokens table.
userID is the primary_key in Seller table.

I have tried to write a trigger but i am having a syntax error by phpMyadmin.

This is the code:

DELIMITER //
CREATE TRIGGER my_trigger
ON Seller
AFTER UPDATE
AS BEGIN
SET NOCOUNT ON
IF UPDATE (email) 
  BEGIN
      DELETE a FROM api_tokens a
      INNER JOIN Seller s ON s.userID = a.sellerId
      Where UPDATED.userID = a.sellerId 
  END 
END

The trigger is not getting saved so i cannot conclude that this is working or not.

Getting syntax error by phpMyadmin but i cannot figure out what is the actual issue.

Sharing error details as well :

#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 ‘ON Seller

Tags:

2

Answers


  1. 0

    Are you sure you mean MYSQL and Not MSSQL there are many parts that are only valid in mssql

    If you still want mysql this works

    DELIMITER //
CREATE TRIGGER my_trigger
AFTER UPDATE
ON Seller FOR EACH ROW
BEGIN
    IF NEW.email <> OLD.email then
          DELETE a FROM api_tokens a
          INNER JOIN Seller s ON s.userID = a.sellerId
          Where NEW.userID = a.sellerId ;
    END IF;
END//
DELIMITER ;
    Login or Signup to reply.
  2. 0

    There is no updated, you want new.

    I also don’t think the join is needed, just:

      DELETE a
      FROM api_tokens a
      WHERE NEW.userID = a.sellerId ;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search