Examples of SQL Server triggers:--the secondary deletion of course table data, the use of triggers to replace the delete operation, first delete the foreign key table related data, and then delete the course. It's hard to understand

Create TriggerCourse_delete onCourseinstead of Delete as        Declare @cno varchar( -)--Defining Variables    Select @cno =Cno fromDeleted--the information in the temp table is instead of substitution delete information to delete    Delete  fromScorewhereCno= @cno    --instead of code commands executed after the replace operation    Delete  fromCoursewhereCno=@cnoGoDelete  fromCoursewhereCno='3-245'---' 3-245 ' is the input parameterSelect * fromscoreSelect * fromCourse--The above trigger stored procedure can only delete one piece of information        Drop TriggerCourse_deleteCreate TriggerCourse_delete onCourseinstead of Delete as    Delete  fromScorewhereCnoinch(SelectCno fromdeleted)Delete  fromCoursewhereCnoinch(SelectCno fromdeleted)GoDelete  fromScorewhereCno='3-105'Select* fromCourseSelect* fromscore--This trigger can delete more than one piece of information--To delete the course table data, use the trigger to replace the delete operation, delete the foreign key table related data, and then delete the course--back up data to backup table before deletingDrop TriggerScore_deleteDrop TableScorebakCreate TableScorebak--Build a backup table(IDsint Identity(1,1)Primary Key, SnoChar(3), CNOChar(5), Degreedecimal(4,1), DateTimesdatetime)Create TriggerScore_delete--A stored procedure that builds a trigger onScore--in the score tableinstead of Delete               --Replace the Declete *from Course command and execute the trigger stored procedure as    Declare @count int   --Define a variable    Select @count=Count(*) fromDeleted--assign the number of rows in the temporary table to the @count variable. The information in this table that was deleted is in the temporary table of deleted.                                           --pay the number of rows in the table to the variable    Declare @i int   --define a variable again    Set @i=0         --For (i=0;i<count;i++)     while @i<@count --iterate through the data of the deleted temporary table, and then transfer    begin        Declare @sno varchar( -)--define three variables for a temporary table        Declare @cno varchar( -)        Declare @degree decimal(4,1)        Select Top 1 @sno=Sno@cno=Cno@degree=Degree fromDeleted--display the previous row of a temporary table        whereSno not inch(Select Top(@i) Sno fromDeleted--if @i is 2, remove the first two pages and show        orCno not inch(Select Top(@i) CNO fromdeleted)--or degree not in (select Top (@i) degree from deleted)               Insert  intoScorebakValues(@sno,@cno,@degree,getdate())--inserting data into the staging table                Delete  fromScorewhereSno=@sno  andCno=@cno --inserts a single piece of data into the temporary table, removing the data from the score tables                   --because the course table is to be deleted, and the course table is the primary key, the information for the Foreign Key score table is deleted, and the score table information is not required to be deleted .                   --We're going to build a temporary table to add the information to the score tables.        Set @i=@i+1  --equivalent to i++    End    Go Delete  fromscoreSelect* fromScorebakDrop TriggerScore_deleteSelect * fromscoreSelect* fromCourseInsert  intoScoreValues(1,'3-105', -)Insert  intoScoreValues(2,'3-105', -)Insert  intoScoreValues(3,'3-105', -)Insert  intoScoreValues(4,'3-105', -)Insert  intoScoreValues(5,'3-105', -)Insert  intoScoreValues(6,'3-105', -)

--Business: To ensure the complete implementation of the entire process, all without problems, unified submission, once there is a problem, back to the origin. Like bank transfers.

Begin Tran--z The start of the transaction--start writing the process statement--After the statement is finished

If @ @ERROR >0

Rollback tran--ROLLBACK TRANSACTION

Else

Commit Tran--ti COMMIT Transaction

BEGIN Tran

INSERT into Scorebak values (@sno, @cno, @degree, GETDATE ())--Ensure the correct execution of these two sentences

Delete from score where [email protected] and [email protected]

If @ @ERROR >0

Begin

Rollback Tran

End

Else

Begin

Commit Tran

End

--Database design: Paradigm theory

1, the data in each column must be single

2. A table must have a primary key

3, the Foreign key table only appears in the primary key table in the primary key column is good, others do not appear.

When there are more than 3 columns and more data in a table, it is necessary to take these columns out to create a separate table, set a primary key, and then simply show the primary key in the original table.

Examples of SQL Server triggers:--the secondary deletion of course table data, the use of triggers to replace the delete operation, first delete the foreign key table related data, and then delete the course. It's hard to understand