Search Posts

MYSQL trigger for Cascading Delete : Workaround needed

Consider 3 Tables –

Student(Id INT NOT NULL PRIMARY KEY, Name VARCHAR(100));
Test(Id INT NOT NULL PRIMARY KEY, Name VARCHAR(50), MaxMarks INT, TestDate DATETIME);  
StudentTakesTest(TestId INT NOT NULL REFERENCES Test(Id), StudentId INT NOT NULL REFERENCES Student(Id), MarksObtained FLOAT, MarksObtainedInPercentage FLOAT);

Every time a student takes test, the average of all the tests taken by a particular student is stored in an index table. I am doing this with the help of triggers.

I have applied 3 triggers on StudentTakesTest table that is AFTER INSERT, AFTER UPDATE and AFTER DELETE. The INSERT and UPDATE Triggers are firing since the actions are done directly on the StudentTakesTest table. The DELETE trigger won’t fire since the action is not directly on the StudentTakesTest table, it is a Cascading delete that happens when an entry in the Test table is deleted.

Is there a work around or a logic that could help me run the trigger on Cascading Delete of the table entries as well?

Source: Stackoverflow

Leave a Reply

Your email address will not be published. Required fields are marked *