SQL/MySQL/Transaction/TRANSACTION ROLLBACK

ROLL Back to the save point

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;

TRANSACTION and ROLLBACK

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;

TRANSACTION and save point

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;