Differences between an After trigger and an instead trigger

The instead trigger is used to replace the common trigger action. When an insert, update, or delete operation is performed on a table, the system does not directly execute these operations on the table, but instead delivers the operation content to the trigger, let the trigger check whether the operation is correct. Perform operations only if they are correct. Therefore, the instead of trigger action must be processed earlier than the Table constraint.
The operation of the instead trigger is somewhat similar to the Integrity Constraint. When operating databases, you can use constraints in some cases to achieve better results. If you use triggers, you can define constraints that are more complex than integrity constraints. For more information about the comparison between triggers and constraints, see books online.
An instead of trigger can be defined not only on a table, but also on a view with one or more base tables, but on a table that serves as a cascading Integrity Constraint target.
The After trigger defines the operations performed after the insert, update, or delete statements are executed on the table. For example, after updating the data in a table, you must immediately perform the specified operation on the relevant table. In this case, you can use the after trigger. The After trigger can only be specified on the table, and the action is later than the constraint processing.
Only one instead of trigger can be created for each table, but multiple after triggers can be created.
Instance:
Instead of trigger:
When you insert data to the table "0261", check whether the student ID exists in the table "". If yes, perform the insert operation. Otherwise, no data is inserted.

SQL code

Create trigger [checkid] on [DBO]. [count to 0261] instead of insertasif not exists (select * from Count to 026 where student ID = (select student ID from inserted) Begin rollback transaction print 'the student ID to be processed does not exist! 'Endelse begin insert into count as 0261 select * From inserted print 'records processed successfully! 'End

After trigger:
An Insert trigger that sets the after (for) type for the ordering table. This trigger is used to automatically calculate the statistical value to the ordering statistical table when inserting records.

SQL code

Create trigger [orderinsert] on [DBO]. [order table] After insertasdeclare @ bookid int, @ ordernum int, @ num intselect @ bookid = book number, @ ordernum = quantity from insertedselect @ num = count (Book Number) from order statistics table where book no. = @ bookidif @ num = 0 -- the book is not found, insert record insert into order statistics table values (@ bookid, @ ordernum) else -- find the book, update Record Update order statistics table set total order volume = total order volume + @ ordernum where book no. = @ bookid