Database-transaction: Cascade Delete (Student Teacher information table) as an example

--things: To ensure the complete implementation of the entire process, all without problems of unified submission, once there is a problem, back to the original point--A stored procedure or trigger with BEGIN TRAN--the beginning of a transaction--start Write process statement--After you finish writing the statementif@ @ERROR >0rollback--rolling back a transactionElseCommit Tran--Commit a transaction---------------------------------cascading deletions as an example drop table Scorebakdrop trigger Score_delete--before deleting, back up the data to the backup table before deleting the CREATE table Scorebak (IDsintIdentity1,1) primary key, Sno varchar ( -), CNO varchar ( -), Degreedecimal(4,1), Dayetime datetime) CREATE TRIGGER Score_delete--Backup and delete--Scientific Name: Cascade Delete on Scoreinstead of delete asDECLARE @countint    Select@count = COUNT (*) fromdeleted declare @iint    Set@i =0     while@i<@count BEGIN DECLARE @sno varchar ( -) declare @cno varchar ( -) DECLARE @degreedecimal(4,1)                SelectTop1@sno =sno, @cno =cno, @degree =degree fromdeletedwhereSno notinch(SelectTop (@i) Sno fromdeleted) or CNO notinch(SelectTop (@i) CNO fromdeleted) BEGIN Tran---------------------------------------------------INSERT into Scorebak values (@sno, @cno, @degree, GETDATE ()) Delete fromScorewhere[email protected] and cno=@cnoif@ @ERROR >0-------------------------------------------------begin rollback Tran--------------------------------------------endElse---------------------------------------------------------Begin commit Tran----------------------------------------------EndSet@[email protected]+1EndgoSelect* fromScorebakSelect* fromScoredelete fromScorewherecno='3-105'

--things: To ensure the complete implementation of the entire process, all without problems of unified submission, once there is a problem, back to the original point--A stored procedure or trigger with BEGIN TRAN--the beginning of a transaction--start Write process statement--After you finish writing the statementif@ @ERROR >0rollback--rolling back a transactionElseCommit Tran--Commit a transaction---------------------------------cascading deletions as an example drop table Scorebakdrop trigger Score_delete--before deleting, back up the data to the backup table before deleting the CREATE table Scorebak (IDsintIdentity1,1) primary key, Sno varchar ( -), CNO varchar ( -), Degreedecimal(4,1), Dayetime datetime) CREATE TRIGGER Score_delete--Backup and delete--Scientific Name: Cascade Delete on Scoreinstead of delete asDECLARE @countint    Select@count = COUNT (*) fromdeleted declare @iint    Set@i =0     while@i<@count BEGIN DECLARE @sno varchar ( -) declare @cno varchar ( -) DECLARE @degreedecimal(4,1)                SelectTop1@sno =sno, @cno =cno, @degree =degree fromdeletedwhereSno notinch(SelectTop (@i) Sno fromdeleted) or CNO notinch(SelectTop (@i) CNO fromdeleted) BEGIN Tran---------------------------------------------------INSERT into Scorebak values (@sno, @cno, @degree, GETDATE ()) Delete fromScorewhere[email protected] and cno=@cnoif@ @ERROR >0-------------------------------------------------begin rollback Tran EndElse---------------------------------------------------------Begin commit Tran----------------------------------------------EndSet@[email protected]+1EndgoSelect* fromScorebakSelect* fromScoredelete fromScorewherecno='3-245'

Database-transaction: Cascade Delete (Student Teacher information table) as an example