Trigger to delete blank rows after INSERT2013-04-09 mysql sql
I have inserted a set of data into a database, now I want to delete all the rows with blank values. How can I do this? Can this be done using triggers?
table BOOKS contains
After inserting data, I want to delete all the rows with empty values in the
author_name column. Here's what I've written so far:
CREATE TRIGGER trigger1 AFTER INSERT ON BOOKS FOR EACH ROW BEGIN DELETE FROM BOOKS WHERE author_name = '' END;
This is not working :(
The issue is that you've got
BEGIN…END around a statement not delimited with a
;. If you put the missing
;, however, you will need to introduce a meta delimiter for the entire definition, because, if you don't do that, the inner
; will break it.
So, put a
; at the end of the
DELETE statement and introduce a
DELIMITER before the trigger definition:
DELIMITER $$ CREATE TRIGGER trigger1 AFTER INSERT ON BOOKS FOR EACH ROW BEGIN DELETE FROM BOOKS WHERE author_name = ''; END $$
Alternatively, since the body contains only one statement, you can simply remove the
CREATE TRIGGER trigger1 AFTER INSERT ON BOOKS FOR EACH ROW DELETE FROM BOOKS WHERE author_name = '';
- Insert into ... values ( SELECT ... FROM ... )
- SQLite - UPSERT *not* INSERT or REPLACE
- Best way to get identity of inserted row?
- Insert results of a stored procedure into a temporary table
- Reset identity seed after deleting records in SQL Server
- MySQL, better to insert NULL or empty string?
- Can I concatenate multiple MySQL rows into one field?