CODEWITHSUNDEEP
mysqlsql
harin04
harin04

Reputation: 281

Trigger to delete blank rows after INSERT

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?

Example:

table BOOKS contains author_name, title, price.

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 :(

Upvotes: 2

Views: 7692

Answers (1)

Andriy M
Andriy M

Reputation: 77707

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 BEGIN…END:

CREATE TRIGGER trigger1
  AFTER INSERT
  ON  BOOKS
  FOR EACH ROW 
DELETE FROM BOOKS WHERE author_name = '';

Upvotes: 1

Related Questions