MySQL Trigger Delete From Table AFTER DELETE

Question

Scope  Two tables   When a new patron is created  they have some information about them stored into a 2nd table  This was done using a trigger as well  it works as expected   Here s an example of my table structure and relationship   Table 1-  patrons   ----- --------- -----     id     name    val                              37    george    x     ----- --------- -----     38    sally     y     ----- --------- -----    Table 2 -  patron info   ---- ----- ----------    id   pid     name      ---- ----- ----------     1    37    george     ---- ----- ----------     2    38    sally      ---- ----- ----------    The administrator can manage the patrons  When they choose to remove a patron  the patron is removed from the table 1 patrons  At this point  nothing happens to table 2 patron info    I m simply trying to create a trigger to delete from table 2  when table 1 has an item deleted  Here s what I ve tried     Initially  I try to drop the trigger if it exists  just to clear the air      DROP TRIGGER IF EXISTS log patron delete    Then I try to create the trigger afterwards     CREATE TRIGGER log patron delete AFTER DELETE on patrons FOR EACH ROW BEGIN DELETE FROM patron info     WHERE patron info pid   patrons id END   At this point  I get a syntax error 1046  Check syntax near END on line 6  I don t know what the error is at this point  I ve tried several different variations  Also  am I required to use a delimiter here   Can anyone help restore my sanity

User · Answer

Why not set ON CASCADE DELETE on Foreign Key patron info pid

User · Answer

I think there is an error in the trigger code  As you want to delete all rows with the deleted patron ID  you have to use old id  Otherwise it would delete other IDs   Try this as the new trigger   CREATE TRIGGER log patron delete AFTER DELETE on patrons FOR EACH ROW BEGIN DELETE FROM patron info     WHERE patron info pid   old id  END   Dont forget the     on the delete query  Also if you are entering the TRIGGER code in the console window  make use of the delimiters also

User · Answer

create trigger doct trigger after delete on doctor for each row delete from patient where patient PrimaryDoctor SSN doctor SSN

[mysql] MySQL Trigger: Delete From Table AFTER DELETE

Examples related to mysql

Examples related to triggers

Examples related to mysql-error-1046