Skip navigation links

MySQL Forums :: Triggers :: MYSQL triggers


Advanced Search

MYSQL triggers
Posted by: Mads Kristensen ()
Date: December 06, 2012 02:20PM

Hello everybody

First off all thank you for a great forum. I really appreciate all the help i get here.

I have posted this code in another threat, but this question is slighty different. I have to make a trigger. The question sounds like this:
"Create a trigger that removes the season if unused whenever information about when and who produces a product is deleted (i.e., whenever a record is removed from the 'Produces' table, check if there are other records about product being produced for the same season, if not, remove also the corresponding record from the season table)"

the MYSQL code is here:

CREATE TABLE IF NOT EXISTS Dvds(
Serial integer NOT NULL,
Name varchar(50),
Year integer,
Genre varchar(50),
Price integer,
PRIMARY KEY (Serial));

CREATE TABLE IF NOT EXISTS Shops(
Id integer NOT NULL,
Name varchar(50),
Address varchar(50),
PRIMARY KEY (Id));

CREATE TABLE IF NOT EXISTS Customers(
CNo integer NOT NULL,
AccNo integer,
Time varchar(50),
PRIMARY KEY (CNo));

CREATE TABLE IF NOT EXISTS ContactPersons(
Id integer NOT NULL,
Name varchar(50),
Phone integer,
PRIMARY KEY (Id));

CREATE TABLE IF NOT EXISTS Seasons(
StartDate date NOT NULL,
EndDate date NOT NULL,
PRIMARY KEY (StartDate,EndDate));

CREATE TABLE IF NOT EXISTS WebShops(
Id integer NOT NULL,
Url varchar(50),
FOREIGN KEY (Id) REFERENCES Shops (Id),
PRIMARY KEY (Id));

CREATE TABLE IF NOT EXISTS Producer(
Id integer NOT NULL,
Address varchar(50),
Name varchar(50),
PRIMARY KEY (Id));

CREATE TABLE IF NOT EXISTS Sold(
Id integer NOT NULL,
CNo integer NOT NULL,
Serial integer NOT NULL,
FOREIGN KEY (Id) REFERENCES Shops (Id),
FOREIGN KEY (CNo) REFERENCES Customers (CNo),
FOREIGN KEY (Serial) REFERENCES Dvds (Serial),
PRIMARY KEY (Id,CNo,Serial));

CREATE TABLE IF NOT EXISTS Has(
Id integer NOT NULL,
Serial integer NOT NULL,
FOREIGN KEY (Id) REFERENCES Shops (Id),
FOREIGN KEY (Serial) REFERENCES Dvds (Serial),
PRIMARY KEY (Id,Serial));

CREATE TABLE IF NOT EXISTS Has2(
Serial integer NOT NULL,
Producer_Id integer NOT NULL,
StartDate date NOT NULL,
EndDate date NOT NULL,
ContactPersons_Id integer NOT NULL,
FOREIGN KEY (Serial) REFERENCES Dvds (Serial),
FOREIGN KEY ( Producer_Id) REFERENCES Producer (Id),
FOREIGN KEY (StartDate) REFERENCES Seasons (StartDate),
FOREIGN KEY (EndDate) REFERENCES Seasons (EndDate),
FOREIGN KEY (ContactPersons_Id) REFERENCES ContactPersons (Id),
PRIMARY KEY (Serial,Producer_Id,StartDate,EndDate,ContactPersons_Id));

I have a feeling of it is something like this:

CREATE TRIGGER `Seasons_before_delete`
AFTER DELETE ON `Seasons`
FOR EACH ROW
BEGIN
DELETE FROM seasonstart
WHERE seasonstart.???????
DELETE FROM seasonend
WHERE seasonend.??????
END

but i really dont know. My teacher told me that i could use "IF" , ELSEIF, GOTOEND? But am i on the right track? Im really blank right now what to do, so hope someone have a suggestion, what i could do to solve this?

Best Regards
Mads

Options: ReplyQuote


Subject Views Written By Posted
MYSQL triggers 1786 Mads Kristensen 12/06/2012 02:20PM
Re: MYSQL triggers 740 Mads Kristensen 12/06/2012 05:19PM
Re: MYSQL triggers 715 Peter Brawley 12/06/2012 08:13PM
Re: MYSQL triggers 541 Mads Kristensen 12/06/2012 08:23PM
Re: MYSQL triggers 702 Peter Brawley 12/06/2012 10:00PM
Re: MYSQL triggers 669 Mads Kristensen 12/07/2012 05:07AM
Re: MYSQL triggers 668 Mads Kristensen 12/07/2012 05:25AM
Re: MYSQL triggers 673 Mads Kristensen 12/07/2012 05:38AM
Re: MYSQL triggers 696 Mads Kristensen 12/07/2012 06:50AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.