CUBRID Study Notes 17 transaction rollback, cubrid Study Notes

CUBRID Study Notes 17 transaction rollback, cubrid Study Notes

Syntax: ROLLBACK [WORK]

 

The following statement will report an error

Alter table code DROP s_name;

Insert into code (s_name, f_name) VALUES ('D', 'Diamond ');

 

ERROR: s_name is not defined.

Roll back to repair broken wounds

Rollback work;

Come back

Alter table code drop s_name;

Insert into code (f_name) VALUES ('Diamond ');

 

Commit work;

 

Partial rollback

You can set a tag to roll back to the specified tag location.

 

Method 1 SAVEPOINT mark;

Mark:

_ A SQL identifier

_ A host variable (starting :)

 

Method 2

 

ROLLBACK [WORK] [TO [SAVEPOINT] mark] [;]

Mark:

_ A SQL identifier

_ A host variable (starting :)

 

Example

First, set the two rollback tags sp1 and sp2.

Create table athlete2 (name VARCHAR (40), gender CHAR (1), nation_code CHAR (3), event VARCHAR (30 ));

Insert into athlete2 (name, gender, nation_code, event)

VALUES ('lim Kye-sook', 'w', 'kor', 'hockey ');

SAVEPOINT SP1;

 

SELECT * from athlete2;

Insert into athlete2 (name, gender, nation_code, event)

VALUES ('lim Jin-suk', 'M', 'kor', 'handball ');

 

SELECT * FROM athlete2;

SAVEPOINT SP2;

 

Rename table athlete2 AS sportsman;

SELECT * FROM sportsman;

Rollback work to SP2;

The above table RENAME operation will be rolled back

 

 

SELECT * FROM athlete2;

Delete from athlete2 WHERE name = 'lim Jin-suk ';

SELECT * FROM athlete2;

Rollback work to SP2;

The above deletion will not be executed and will be rolled back.

 

 

SELECT * FROM athlete2;

Rollback work to SP1;

SELECT * FROM athlete2;

Commit work;

This is an example of rollback to sp1.