SQL Server trigger and SQL Server trigger

SQL Server trigger and SQL Server trigger

Brief Introduction:

A trigger is a special stored procedure, which is automatically executed only when a specific event occurs. Stored Procedures and triggers are a collection of SQL statements and flow control statements. stored procedures are called directly by the name of stored procedures, and triggers are executed mainly through time triggering.

Trigger category:

Triggers include DML triggers and DDL triggers. DML triggers are common triggers that are automatically executed when a data operation language (DML) event occurs on the database server. DDL triggers are a new type of triggers, it is triggered in response to a Data Definition Language (DDL) statement and is generally used to execute management tasks in a database. Today we are mainly studying DML triggers, which are divided into two types: AFTER triggers and INSTEAD triggers.

An AFTER trigger is an execution that is activated AFTER the record has been changed, that is, the trigger that has ended the event is triggered. An instead of trigger is used to replace the original operation and trigger before the event occurs, in this way, it does not execute the original SQL statement, but operates according to the definition in the trigger.

Trigger condition:

DML triggers are activated in response to UPDATE, INSERT, and DELETE statements. DDL triggers are activated in response to CREATE, ALTER, DROP, GRANT, DENY, REVOKE, UPDATE, STATISTICS, and other statements.

Mind Map:

Create a trigger:

Simple application:

Take deleting a news category in the niugu news publishing system and deleting news and comments at the same time as an example. Here, due to foreign key constraints, you need to delete news and comments from this category at the same time. Because the AFTER trigger is activated AFTER the record has been changed, we use the INSTEAD trigger to replace the previous simple delete category operation with the Delete comment, news, and category operation. In this way, foreign key constraints will not cause deletion failure.

<Span style = "font-family: KaiTi_GB2312; font-size: 18px; "> -- ============================================ =========-- Author: <Yi hongxin> -- Create date: <2014-8-18> -- Description: <delete category trigger> -- ====================================== ============= alter trigger [dbo]. [trigCategoryDelete] ON [dbo]. [category] instead of DELETEAS BEGINdeclare @ caId intselect @ caId = id from deleted -- delete comment where newsId in (select newsId from news where caId = @ caId) -- delete news delete where caId = @ caId -- delete category where id = @ caId END </span>

Trigger function:

1) Enforce data integrity that is more complex than the CHECK Constraint

2) use custom error message 3) implement cascade modification of multiple tables in the database 4) Compare the status of data before and after database modification 5) call more stored procedures 6) there are still many advantages for maintaining the nonstandard data trigger. We need to explore it in practical applications. We hope you will criticize and correct the above shortcomings. 
SQL Server trigger Problems

Careate trigger trigger_name on B
For insert
As
Declare @ a varchar (20 ),
Seelct @ a = name from inserted // Note: inserted and deleted are special tables of SQLServer triggers.
If not exists (select name from A where name = @)
Begin
Rollback transaction
Else
Commit
End
Go

How do I write triggers in SQL server?

Take a closer look at this Code:
Update BorrowRecord
Set br. StudentID = I. StudentID
From BorrowRecord br, Deleted d, Inserted I
Where br. StudentID = d. StudentID

From BorrowRecord br, Deleted d, Inserted I
Use the alias br to replace BorrowRecord, use d to replace Deleted, and use d to replace Inserted.

Do you understand?

Good Luck!