0

I have a table where I can't duplicate a row because one of the fields has a stored procedure, named items.calcPrice, on it that no longer exists.

Here's the MySQL command:

INSERT INTO items (folder_id)
VALUES (56)

the error message says, "Couldn't write row. FUNCTION items.calcPrice does not exist."

How do I identify and delete this stored procedure on my table? I have 2 fields which have a "price" value so I'm not sure which field this procedure is operating on.

user229044
  • 232,980
  • 40
  • 330
  • 338
tim peterson
  • 23,653
  • 59
  • 177
  • 299
  • Please paste the statement you are executing which gives rise to that error. – eggyal May 10 '12 at 13:55
  • i'm using mysqlPro so i just have to click a button to create a new row, but give me a second to reproduce with a SQL command... – tim peterson May 10 '12 at 13:59
  • Are there any before/after insert triggers on that table? `SHOW TRIGGERS WHERE Event = 'INSERT' AND Table = 'mytable'`? – eggyal May 10 '12 at 14:02
  • hmm, its saying, `SHOW TRIGGERS WHERE Event = 'INSERT' AND Table = 'mytable'` isn't the right syntax, is that MySQL compatible? – tim peterson May 10 '12 at 14:07
  • sorry to those reading this thread, its **not** mysqlPro but rather Sequel Pro – tim peterson May 10 '12 at 14:09
  • Yes - [see the manual](http://dev.mysql.com/doc/en/show-triggers.html). Just do `SHOW TRIGGERS` without the `WHERE` clause and see what turns up. – eggyal May 10 '12 at 14:10
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/11108/discussion-between-eggyal-and-tim-peterson) – eggyal May 10 '12 at 14:11

1 Answers1

1

Following our discussion in chat, it appears you have a BEFORE INSERT trigger called Items_Trigger defined on this table, which attempts to call the function to which your question refers.

To delete the trigger:

DROP TRIGGER Items_Trigger;
eggyal
  • 122,705
  • 18
  • 212
  • 237
  • 1
    Do not delete triggers until you know why they are there. – HLGEM May 10 '12 at 14:45
  • 1
    @HLGEM: Agreed - in this case, we covered that in our chat: "*i can delete all this stuff i don't need it anymore*" and, on further prompting, "*it's old logic we don't need anymore*". – eggyal May 10 '12 at 14:46
  • 1
    I just wanted future readers to be aware that this is a dangerous practice if you don't know that the trigger is no longer needed. – HLGEM May 10 '12 at 14:49
  • @HLGEM thanks for your concern. These were very old rules which we aren't using anymore. Thanks for the note for future readers. – tim peterson May 10 '12 at 15:04