BEFORE INSERT trigger > how to rollback the insertion
Posted by: Jakub Małecki
Date: April 01, 2015 12:04PM

Hi, I have a simple table in my database - there are only 2 fields: [ID] (AI, PK) and [Name]. My intention is to create a BEFORE INSERT trigger to check if there already exists a record in the table with the same value in the [Name] field as the new one I'm trying to insert. If it exists the insertion should be cancelled.
This is what I have:
CREATE DEFINER = 'root'@'localhost' TRIGGER `aplikacje`.`ogloszeniodawca_BEFORE_INSERT` BEFORE INSERT ON `ogloszeniodawca` FOR EACH ROW
BEGIN

DECLARE v_ogloszeniodawca VARCHAR(60);
SELECT o.ogloszeniodawca INTO v_ogloszeniodawca
FROM ogloszeniodawca AS o
WHERE o.ogloszenodawca=NEW.ogloszeniodawca;

IF NOT v_ogloszeniodawca=NULL THEN
SELECT 'Already exists in the table.';
END IF;

/* Now - how to cancel the insertion? */

END

I don't know how to cancel the insertion then. Is the above concept right?

Options: ReplyQuote


Subject
Written By
Posted
BEFORE INSERT trigger > how to rollback the insertion
April 01, 2015 12:04PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.