Mysql – How to insert into junction table using triggers

innodbMySQLtrigger

Sorry in advance if this is "basic SQL." I wanted to know how to update my junction tables automatically. For example, these are my tables.

Artist and Song are base tables and SongArtist is the junction table. Everything in SongArtist is PK and FK.

CREATE  TABLE IF NOT EXISTS `Artist` (
  `artistID` INT NOT NULL AUTO_INCREMENT ,
  `artistName` VARCHAR(150) NOT NULL ,
  PRIMARY KEY (`artistID`) )
ENGINE = InnoDB

CREATE  TABLE IF NOT EXISTS `Song` (
  `songName` VARCHAR(150) NOT NULL ,
  `songID` INT NOT NULL AUTO_INCREMENT ,
  PRIMARY KEY (`songID`) )
ENGINE = InnoDB

CREATE  TABLE IF NOT EXISTS `SongArtist` (
  `songID` INT NOT NULL ,
  `artistID` INT NOT NULL ,
  PRIMARY KEY (`songID`, `artistID`) ,
  INDEX `fk_Artist_Artist_idx` (`artistID` ASC) ,
  INDEX `fk_Song_Song_idx` (`songID` ASC) ,
  CONSTRAINT `fk_Song_Song`
    FOREIGN KEY (`songID` )
    REFERENCES `Song` (`songID` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_Artist_Artist`
    FOREIGN KEY (`artistID` )
    REFERENCES `Artist` (`artistID` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB

I created some triggers like this, but they don't seem to work as I can't do INSERT INTO and add a new row when I only know one field of the junction table because I have two columns that are PK.

CREATE 
TRIGGER after_song_insert AFTER INSERT
ON Song
FOR EACH ROW 
BEGIN
    INSERT INTO SongArtist (songID) values (songID);
END;
CREATE
TRIGGER after_song_update AFTER UPDATE
ON Song
FOR EACH ROW 
BEGIN
    INSERT INTO SongArtist (songID) values (songID);
END;
CREATE
TRIGGER after_song_delete AFTER DELETE
ON Song
FOR EACH ROW 
BEGIN
    DELETE FROM SongArtist (songID) values (songID);
    END;
$$


DELIMITER ;

What should I do?

Best Answer

You have to think in terms of the design.

After triggers on the Song table cannot help populate the SongArtist table since Song and Artist have no immediate relationship.

Although you should not need this, you could have before triggers on SongArtist to check for validity of SongID and ArtistID.

DELIMITER $$
CREATE TRIGGER before_songartist_insert
BEFORE INSERT ON SongArtist
FOR EACH ROW 
BEGIN
    DECLARE song_ok,artist_ok,sum_ok INT;
    SELECT COUNT(1) song_ok   FROM Song WHERE songID = NEW.songID;
    SELECT COUNT(1) artist_ok FROM Artist WHERE artistID = NEW.artistID;
    SET sum = song_ok + artist_ok;
    IF sum_ok < 2 THEN
        SELECT COUNT(1) INTO sum_ok FROM information_schema.dummy;
    END IF;
END; $$
CREATE TRIGGER before_songartist_insert
BEFORE UPDATE ON SongArtist
FOR EACH ROW 
BEGIN
    DECLARE song_ok,artist_ok,sum_ok INT;
    SELECT COUNT(1) song_ok   FROM Song WHERE songID = NEW.songID;
    SELECT COUNT(1) artist_ok FROM Artist WHERE artistID = NEW.artistID;
    SET sum = song_ok + artist_ok;
    IF sum_ok < 2 THEN
        SELECT COUNT(1) INTO sum_ok FROM information_schema.dummy;
    END IF;
END; $$
DELIMITER ;

In reality, the ON DELETE CASCADE and ON UPDATE CASCADE clauses should make using triggers unnecessary. You should insert rows into the Song and Artist as a single transaction. Then, using songIDs and artistIDs from the completed transaction, you can insert them into SongArtist and let any defined CONSTRAINT check the validity.

Related Question