MySql Trigger before insert not working

Question

I am having trouble creating a BEFORE INSERT trigger.

table schema:

CREATE TABLE IF NOT EXISTS `myReferenceTable` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `linkFrom` bigint(20) NOT NULL,
  `linkTo` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `linkFrom` (`linkFrom`,`linkTo`),
  KEY `linkTo` (`linkTo`)
) ENGINE=InnoDB

inserting data:

INSERT INTO `myReferenceTable` (`id`, `linkFrom`, `linkTo`) VALUES
(1, 1, 2), // allowed
(2, 2, 1); // allowed

My failed attempt at creating a BEFORE INSERT trigger which will not allow linkFrom and linkTo to equal each another. This table articleReferncesTable can not have any article refering to it self,

/* This fails */
create trigger myReferenceTable_noDuplicate
BEFORE INSERT 
ON myReferenceTable
FOR EACH ROW
BEGIN
 IF NEW.linkFrom = NEW.linkTo
  insert ignore()
 END IF;
END;

Example:

INSERT INTO `myReferenceTable` (`id`, `linkFrom`, `linkTo`) VALUES
(3, 1, 1), // should fail
(4, 2, 2); // should fail

the above data is not allowed. So i want this table to be a "set" table the following data is allowed:

INSERT INTO `myReferenceTable` (`id`, `linkFrom`, `linkTo`) VALUES
    (3, 1, 2),  // allowed 
    (4, 1, 3); // allowed

score 2 · Answer 1 · edited Sep 19 '12 at 11:35

Triggers are used to data integrity and avoid data . use triggers for deleting more than two tables also. Before initialing triggers we to change mysql delimiter operator temporarily. Because Triggers use semicolon (;) operator to multiple sql commands.

In your case use the follow commands one by one:

Step 1:

Change delimiter,

delimiter $$

Step 2:

Create trigger,

  CREATE TRIGGER `blog_before_delete`     
  AFTER DELETE ON `blog`     
  FOR EACH ROW     
 BEGIN
  DELETE FROM blog_tags where blogid = OLD.id;
        DELETE FROM blog_comments where blogid = OLD.id;
 END
 $$

Step 3:

Restore delimiter,

delimiter ;

Explanation :

Here the OLD is a keyword and refers to the blog table row that we need to delete. Mysql initiates the trigger blogbeforedelete whenever we delete a entry in the blog table. All the tags related to the blog are deleetd and all the comments related to the blog are deleted. This ensures that no unwanted data exists in the database. Also the process is automatic.

(source)

score 1 · Accepted Answer · edited May 23 '17 at 12:11

1

INSERT ignore() doesn't cancel the insert or cause it to fail. There are a few ways to do this, but the simplest is to cause an error:

...
IF NEW.linkFrom = NEW.linkTo
    DECLARE dummy INT;
    SELECT LINKFROM_EQUALS_LINKTO INTO dummy FROM links
    WHERE links.id = new.id;
END IF;
...

see TRIGGERs that cause INSERTs to fail? Possible?

edited May 23 '17 at 12:11

Community

1
1

answered Jan 22 '11 at 08:49

gregjor

21,802
1
23
16

MySql Trigger before insert not working

2 Answers2