CODEWITHSUNDEEP
mysqlsql
aneeb
aneeb

Reputation: 95

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

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

Upvotes: 1

Views: 52

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269703

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 ;

Upvotes: 1

nbk
nbk

Reputation: 49375

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 ;

Upvotes: 1

Related Questions