CODEWITHSUNDEEP
mysqldatabase-trigger
Orion
Orion

Reputation: 39

MySQL create table and trigger

Can anyone help me understand what this code does?

CREATE TABLE `exams` (

`id` int(11) NOT NULL, `score` int(2) NOT NULL, `class` int(11) NOT NULL, `date` date NOT NULL, PRIMARY KEY (`class`,`date`,`id`));

CREATE TRIGGER `trig3` BEFORE INSERT ON `exams` FOR EACH ROW SET @xxx = @xxx + 1;

SET @xxx = 0;

INSERT INTO `exams` VALUES (10001,24,1,'2013-09-16'), (10005,30,1,'2013-09-16'), (10083,30,1,'2014-03-21'), (10120,26,1,'2014-03-21'), (10035,23,1,'2014-07-22'), (10005,28,2,'2014-01-23'), (10001,27,2,'2014-06-30'), (10001,25,4,'2014-01-23'), (10083,22,4,'2014-01-23'), (10120,30,4,'2014-01-23'), (10005,22,4,'2014-03-21');

SELECT @xxx;

I understand the creation of the table and the insertion of values but i don't get the rest of it.

Upvotes: 0

Views: 92

Answers (1)

Raymond Nijland
Raymond Nijland

Reputation: 11602

The code seams to be a workaround way to count or check if the multi insert inserted all records or not. 

    SELECT @xxx;

| @xxx |
| ---- |
| 11   |

But MySQL doesn't have a native way to find that out.

see demo

A more easy MySQL code to do the same without needing the TRIGGER code and SET.
But it will require a new table structure to function. 

CREATE TABLE `esami` (
    `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY
  , `id_studente` int(11) NOT NULL
  , `voto` int(2) NOT NULL
  , `id_corso` int(11) NOT NULL
  , `data` date NOT NULL
  , UNIQUE KEY (`id_corso`,`data`,`id_studente`)    
);

And the query to test if all records where inserted by the multi insert. 

SELECT
  COUNT(*)
FROM
  esami
WHERE
  id BETWEEN LAST_INSERT_ID() AND LAST_INSERT_ID() + 11;

see demo

Upvotes: 2

Related Questions