CODEWITHSUNDEEP
mysqlsqltriggers
ghTvNath
ghTvNath

Reputation: 357

Update table on condition from another table Trigger

I have a two tables

MovieStar (
    name: char(15),
    address:varchar(40),
    gender:char(1),
    birthdate: date,
    rating:float
)

StarsIn (
    movieTitle:char(25), 
    movieYear:int, 
    starname:char(15), 
    role:varchar(15)
)

starName is foreign key which is related to name of MovieStar.

I want to increment rating by one when Starsin record is inserted and role of the new record is 'main'

What it would do is; 

on insert to starsin, check whether role = 'main'
if so, what is starname
increment rating of moviestar name = starname

Upvotes: 1

Views: 1695

Answers (2)

Tony
Tony

Reputation: 10327

I would not store the value rating in your table as it can be derived from the other table's data when it is required. And why are you using float for the count?

Create a VIEW which counts the rows in Starsin, based on your condition role = 'main' and then you have no need for the trigger and the count will always be up to date when new rows are added or removed.

With the trigger solution you also have to take account of row deletion to decrement the count.

EDIT: From the comment you made, here's a sample trigger (although the syntax may be wrong as I mainly work with SQL Server). I'm assuming the name field in the MovieStar table is a primary key and therefore unique.

CREATE TRIGGER UpdateRating AFTER INSERT ON StarsIn
FOR EACH ROW
BEGIN
    UPDATE MovieStar SET rating =
        (SELECT rating FROM MovieStar WHERE name = NEW.starname) + 1
    FROM MovieStar INNER JOIN NEW ON MoveStar.name = NEW.starname
    WHERE NEW.role = 'main'
END

I'm not familiar with MySQL, I work with SQL Server, where triggers need to be set based. I'm guessing the FOR EACH ROW part of the trigger means the statements are executed for each row inserted, but I may be wrong about that.

Upvotes: 4

aF.
aF.

Reputation: 66687

You need to create a trigger on insert and do the intended update.

Upvotes: 2

Related Questions