1

Hey i am trying to create a trigger that will delete all the relations to a particular band_id given, The first table is bands and the second table is releases,the column with single integers is the band_id and so i need to create a trigger to delete the band_id and all its relations from both tables

1   Radiohead
2   Prodigy
3   Kylie Minogue
4   Keith Washington
5   Nick Cave
6   Robbie Williams
7   Pj Harvey
8   Catatonia
9   Manic Street Preachers

cd001   1
cd002   1
cd003   1
cd004   1
cd006   1
cd007   1
cd008   1

so far i have attempted created a trigger and have:

CREATE TRIGGER Updates
AFTER INSERT ON bands
REFERENCING new row band_id
FOR EACH row
when band_id is NOT null
DELETE FROM bands WHERE band_id = 1 ;

This should in theory delete at least from the bands table the band_id 1 however it has an error can anyone see a problem with this or give any advice?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user3004630
  • 27
  • 3
  • 1
    `Kylie Minogue` is a band? – Kermit Apr 09 '14 at 18:12
  • 1
    @Kermit of course. And prettier than Nick Cave. – ypercubeᵀᴹ Apr 09 '14 at 18:13
  • Have you read the [manual](http://msdn.microsoft.com/en-us/library/ms189799.aspx "CREATE TRIGGER (Transact-SQL)")? – Andriy M Apr 09 '14 at 18:17
  • Wouldn't it be easier to set up the relationship to have a [cascade delete](http://www.mssqltips.com/sqlservertip/2743/using-delete-cascade-option-for-foreign-keys/)? I find triggers very hard to find and troubleshoot when something goes wrong... [So do at least a few others](http://stackoverflow.com/questions/1457013/cascade-on-delete-or-use-triggers). – David Apr 09 '14 at 18:17
  • the question to be answered is: Create a trigger that will automatically remove all related band_member re cords when a band is removed from the band table the error is with the referencing as not entirley sure what it does and i think we have to use the table as created which contains no cascades – user3004630 Apr 09 '14 at 18:37
  • If you want to cascade deletes, why have you written an `INSERT` trigger? If you want someone to help you, at least answer their questions (like the one above, ***"which DBMS you use, are you sure it's SQL-Server?"*** – ypercubeᵀᴹ Apr 09 '14 at 18:39
  • Sorry i am not familiar with triggers i just presumed that the insert indicated the trigger to be done after the insert of the table being created, again sorry i tried to answer most of the questions however cannot see the comment which you are referring to – user3004630 Apr 09 '14 at 18:46
  • i am not sure if it is sql server the example i was using was from this web- page :https://db.apache.org/derby/docs/10.2/ref/rrefsqlj43125.html – user3004630 Apr 09 '14 at 18:51
  • OK< first find out which DBMS you are using. Is it SQL-Server, MySQL, Oracle, Postgres, Derby, Firebird, SQLite, ...? There are a lot of them out there and the syntax for triggers differs. – ypercubeᵀᴹ Apr 09 '14 at 19:16
  • What does `SELECT @@VERSION ;` show? – ypercubeᵀᴹ Apr 09 '14 at 19:18
  • I am using MySQL and where are you getting the SELECT @@version from? – user3004630 Apr 10 '14 at 11:11

0 Answers0