SQL/MySQL/Transaction/TRANSACTION ROLLBACK

ROLL Back to the save point

   <source lang="sql">

Drop table Books;

CREATE TABLE Books (

  ID SMALLINT NOT NULL PRIMARY KEY,
  Title VARCHAR(60) NOT NULL,
  Copyright YEAR NOT NULL

) ENGINE=INNODB;

select * from Books; START TRANSACTION; INSERT INTO Books VALUES (103, "Angels", 1966); INSERT INTO Books VALUES (104, "Black", 1932); SAVEPOINT sp1; INSERT INTO Books VALUES (105, "None", 1996); INSERT INTO Books VALUES (106, "A", 1980); ROLLBACK TO SAVEPOINT sp1; INSERT INTO Books VALUES (107, "Postcards", 1992); INSERT INTO Books VALUES (108, "The", 1993); COMMIT; select * from Books;

      </source>
   
  

TRANSACTION and ROLLBACK

   <source lang="sql">

Drop table Books;

CREATE TABLE Books (

  ID SMALLINT NOT NULL PRIMARY KEY,
  Title VARCHAR(60) NOT NULL,
  Copyright YEAR NOT NULL

) ENGINE=INNODB;

START TRANSACTION; INSERT INTO Books VALUES (3, "Angels", 1966); INSERT INTO Books VALUES (4, "Black", 1932); ROLLBACK;

select * from Books;

      </source>
   
  

TRANSACTION and save point

   <source lang="sql">

Drop table Books;

CREATE TABLE Books (

  ID SMALLINT NOT NULL PRIMARY KEY,
  Title VARCHAR(60) NOT NULL,
  Copyright YEAR NOT NULL

) ENGINE=INNODB; select * from Books; START TRANSACTION; INSERT INTO Books VALUES (3, "Angels", 1966); INSERT INTO Books VALUES (4, "Black", 1932); SAVEPOINT sp1; select * from Books;

      </source>