Summary of SQL Server triggers

Time:2020-9-29

Introduction of trigger:

Trigger is a method provided by SQL server to programmers and data analysts to ensure data integrity. It is a special stored procedure related to table events. Its execution is not called by the program or started manually, but triggered by events. For example, when a table is operated (insert, delete, update), it will be activated to execute. Triggers are often used to enforce data integrity constraints and business rules. Triggers can be downloaded from DBA_ TRIGGERS ,USER_ In the triggers data dictionary. The trigger of SQL3 is a statement that can be automatically executed by the system to modify the database.

Triggers can query other tables and can contain complex SQL statements. They are primarily used to enforce compliance with complex business rules or requirements. For example, you can control whether new orders are allowed to be inserted according to the customer’s current account status.

Triggers can also be used to enforce referential integrity so that relationships defined between tables are preserved when rows are added, updated, or deleted from multiple tables. However, the best way to enforce referential integrity is to define primary and foreign key constraints in related tables. If you use a database diagram, you can create relationships between tables to automatically create foreign key constraints.

The only difference between a trigger and a stored procedure is that the trigger cannot execute the execute statement call, but rather triggers the execution automatically when the user executes a transact SQL statement.

In addition, flip flop is the basic unit circuit of logic circuit, which has memory function and can be used to store binary data and memorize information.

——From Baidu Encyclopedia

Function of trigger:

  • You can force validation or conversion of data before writing to the data table.
  • When a trigger error occurs, the result of the transaction will be revoked.
  • Some database management systems can use triggers for data definition language (DDL), called DDL triggers.
  • Instead of can be replaced according to specific circumstances.

Advantages of flip flops:

Triggers implement cascading changes through related tables in the database, but they can be performed more efficiently by cascading referential integrity constraints. Triggers can enforce constraints that are more complex than those defined by check constraints. Unlike the check constraint, triggers can reference columns in other tables. For example, triggers can use select in another table to compare inserted or updated data, as well as perform other operations, such as modifying data or displaying user-defined error messages. Triggers can also evaluate the state of the table before and after data modification, and take countermeasures according to the differences. Multiple triggers of the same kind (insert, update, or delete) in a table allow multiple different actions to be taken in response to the same modification statement.

Use trigger carefully:

Trigger has powerful function and can realize many complex functions easily and reliably. Why use it cautiously. Trigger itself has no fault, but because of our abuse, it will make the maintenance of database and application difficult. In database operation, we can realize data operation through relation, trigger, stored procedure and application program At the same time, rules, constraints and default values are also important guarantee for data integrity. If we rely too much on triggers, the structure of database will be affected and the maintenance complexity will be increased.

1: Trigger is a special stored procedure, which can not be called explicitly, but is automatically activated when records are inserted, updated or deleted.

**So triggers can be used to implement complex integrity constraints on tables.

2: SQL server creates two dedicated tables for each trigger: the inserted table and the deleted table.

These two tables are maintained by the system, and they exist in memory rather than in the database. The structure of these two tables is always the same as that of the table affected by the trigger. After the trigger is executed, the two tables associated with the trigger are also dropped.

The deleted table holds all rows to be deleted from the table due to the execution of delete or update statements.

The inserted table holds all rows to be inserted into the table due to the execution of the insert or update statement.

3: Instead of and after triggers

SQL Server 2000 provides two kinds of triggers: instead of and after triggers. The difference between the two triggers is that they are activated differently

Instead of triggers are used to replace the T-SQL statement that causes the trigger to execute. In addition to tables, instead of triggers can also be used for views to extend the update operations that views can support.

After triggers are executed after an insert, update, or deleted statement. Constraint checking and other actions occur before the after trigger is activated. After triggers can only be used for tables.

Each modification action (insert, update and delete) of a table or view can have an instead of trigger, and each modification action of a table can have multiple after triggers.

4: The process of trigger execution

If an insert, update, or delete statement violates the constraint, the after trigger will not be executed, because the constraint check occurs before the after trigger is activated. So after triggers cannot override constraints.

Instead of the action that activates it, instead of executing it. It is executed when the inserted and deleted tables have just been created and no other operations have occurred. Because the instead of trigger executes before the constraint, it can do some preprocessing on the constraint.

5: Using T-SQL statements to create triggers

The basic statement is as follows:

create trigger trigger_name

  on {table_name | view_name}

  {for | After | Instead of }

  [ insert, update,delete ]

  as

  sql_statement

6: Delete trigger:

The basic statement is as follows:

drop trigger trigger_name

7: To view the existing triggers in the database:

–View existing triggers in database

use jxcSoftware

  go

  select * from sysobjects where xtype=’TR’

–View individual triggers

exec sp_ Helptext ‘trigger name’

8: Modify trigger:

The basic statement is as follows:

alter trigger trigger_name

  on {table_name | view_name}

  {for | After | Instead of }

  [ insert, update,delete ]

  as

  sql_statement

9: Trigger instance

Create table student table

use triggerTest;
–Create student table
create table Student
(ID        int
primary key identity(1, 1),
StudentID int — student number
);

Create borrowrecord table

–Create borrowrecord table
create table Borrowrecord
(– student borrowing record form
Borrow record int identity (1,1), — serial number
StudentID – int, – student number
Borrow date – datetime, – lending time
Return date – datetime, – return time

);

Add test data

INSERT into Student(studentID) values(1)
INSERT into Student(studentID) values(2)
INSERT into BorrowRecord(studentID,BorrowDate,ReturnDAte) VALUES(1,getdate(),getdate()+1)
INSERT into BorrowRecord(studentID,BorrowDate,ReturnDAte) VALUES(2,getdate(),getdate()+1)

The writing of trigger

–Write a trigger function: if I change a student’s student number, I hope his loan record is still related to the student (that is, change the student number of the loan record table at the same time);
Create trigger truStudent on student — creates a trigger in the student table
For update — why are events triggered
as

--What to do after the event is triggered
 if update(studentID)
     begin
         Declare @ stuidnew int; -- record the new student ID from the temporary table inserted
         Declare @ stuidold int; -- update the previous old student ID from the temporary table delete record
         select @stuidold = studentID
         from deleted;
         select @stuidnew = studentID
         from inserted;
         update Borrowrecord
           set 
               studentID = @stuidnew
         where studentID = @stuidold;
         print @stuidnew;
         print @stuidold;
 end;

go

Test trigger

–Test trigger
update Student
set

  studentID = 111

where studentID = 1;

The above code is valid for pro test!

Summary:

The key point is to know the two main tables of trigger:

–Trigger operation data changes of deleted table and inserted table — insert operation (insert) inserted table has data, deleted table has no data — delete operation (delete) — inserted table has no data, deleted table has data — update operation (update) — inserted table has data (new data), deleted table has data (old data)

Articles are constantly updatedIf you like, please pick up your lovely hands and give me a compliment!

Like is a positive attitude towards life, like it!