Database trigger base usage (Backup Delete Row + DELETE multiple rows)

--Trigger: A trigger is a special kind of stored procedure, only for "adding and deleting"--The special place of the trigger is that the trigger is triggered by the operation of the database table.--stored procedures are performed by man -made execSelect* fromStudentcreate Trigger Student_insert--create trigger on Student--Specifies the table where the trigger resides, and can also be written as after,for and after are executed after the operation forInsert--automatic execution of triggers when an insert operation is performed asUpdate StudentSetsbirthday='1980-08-20'wheresno= theGoinsert into Student values ( the,'Wang two small','male','1979-09-02',95033) Create trigger Student_delete--create trigger on Studentinstead of delete--replace an action with the procedure of a trigger to replace the deleted action, not being able to delete the student table asUpdate StudentSetsbirthday='1980-08-20'wheresno= theGodelete fromStudentdrop Trigger Student_delete--Delete trigger create trigger Student_delete2--Delete a piece of data on studentinstead of delete asDelete fromScorewheresno= theDelete fromStudentwheresno= theGodelete fromStudentdelete fromCoursewherecno='3-105'CREATE TRIGGER Course_deleteThe CNO in the--course table has a primary foreign key relationship--temporary tables in deleted triggers on courseinstead of delete as    Select* fromDeleted--deleted is equivalent to a virtual temporary table with the data you want to delete go to delete fromCoursewherecno='3-105'Drop Trigger Course_delete--Delete Trigger--Delete Course table data at once, utility trigger replace delete operation, line delete foreign key table related data, delete primary key table Course table related data create trigger Course_delete2--Only one row can be deleted, and if multiple rows are deleted because of the assignment, only the last number assignment is executed, so only the last line is deleted on Courseinstead of delete asdeclare @cno varchar ( -)        Select@cno =CNO Delete fromScorewherecno=@cno Delete fromCoursewherecno=@cnogocreate Trigger Course_delete4--One strip delete multiple lines on Courseinstead of delete asDelete fromScorewhereCnoinch(Selete CNO fromdeleted) Delete fromCoursewhereCnoinch(Selete CNO fromdeleted) Gocreate trigger course_delete3on courseinstead of Delete asDECLARE @countint    Select@coount = COUNT (*) fromdeletedif@count = =1--perform a delete row operation begin declare @cno varchar ( -)        Select@cno =CNO Delete fromScorewherecno=@cno Delete fromCoursewherecno=@cno Endif@count >1--iterate through each row, performing the operation of deleting a row separately begin delete fromScorewhereCnoinch(Selete CNO fromdeleted) Delete fromCoursewhereCnoinch(Selete CNO fromdeleted) Endgodrop 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 Delete on Scoreinstead of delete asDECLARE @countint    Select@count = COUNT (*) fromdeleted declare @iint    Set@i =0     while@i< @count--iterate through the data of the deleted temporary table, and then transfer to Scorebak 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)--or degree notinch(SelectTop (@i) degree fromScore)--two can be determined when you do not have to write all, this problem does not have to write degree notin, paged query each rowINSERT into Scorebak values (@sno, @cno, @degree, GETDATE ()) Delete fromScorewhere[email protected] and cno=@cnoSet@[email protected]+1EndgoSelect* fromScorebakSelect* fromScoredelete fromScorewherecno='3-105'        

Database trigger base usage (Backup Delete Row + DELETE multiple rows)