CODEWITHSUNDEEP
mysqlsqlmysql-workbench
J S
J S

Reputation: 49

Creating a trigger before insert

I am trying to update an entry if already exists using a trigger. I would like to check if an entry exists update if not then insert. When I tried to insert new entry I got an error: 'Can't update table in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

DELIMITER // 
CREATE TRIGGER check_table_before_insert BEFORE INSERT ON Songs FOR EACH ROW BEGIN IF NEW.PageURL =(
  SELECT 
    Songs.PageURL 
  FROM 
    Songs 
  WHERE 
    Songs.PageURL = NEW.PageURL
) THEN 
UPDATE 
  Songs 
SET 
  Songs.Title = NEW.Title, 
  Songs.Album = NEW.Album, 
  Songs.Label = NEW.Label, 
  Songs.Release_Date = NEW.Release_Date, 
  Songs.PageURL = NEW.PageURL, 
  Songs.IsSingle = NEW.IsSingle, 
  Songs.Code = NEW.Code, 
  Songs.ImageURL = NEW.ImageURL, 
  Songs.Link_320 = NEW.Link_320, 
  Songs.Link_128 = NEW.Link_128, 
  Songs.Link_48 = NEW.Link_48, 
  Songs.AlbumURL = NEW.AlbumURL 
WHERE 
  Songs.PageURL = NEW.PageURL;
END IF;
END;
// DELIMITER;

Upvotes: 1

Views: 111

Answers (1)

user149341
user149341

Reputation:

This isn't an appropriate use for a trigger.

If you want to perform a "insert or update" operation on a table, use the INSERT … ON DUPLICATE KEY UPDATE … syntax. It already has the semantics you are looking for.

If the columns you are updating amount to the entirety of the row, you may be able to use the REPLACE verb as an alternative to INSERT. Note that it will delete the row before insetting a new one, so any columns not included in the REPLACE query will be lost.

Upvotes: 1

Related Questions