Database trigger Summary

Concepts, categories, and functions of triggers

A trigger is a special type of stored procedure.

A trigger is executed when an event is triggered, and a stored procedure can be directly called by the stored procedure name.

Trigger has four elements:

Ø name: the trigger has a name that complies with the flag naming rules.

Define goals: triggers must be defined on tables or views.

Ø trigger condition: whether the statement is Update, insert, or delete.

Ø trigger logic: how to handle the trigger.

 

Trigger type

After trigger

The After trigger tells the SQL statement what to do after the insert, update, or delete operation is performed.

Ø instead of trigger

Tells you what other operations are used to replace the insert, update, or delete operation.

Trigger Function

Enhanced constraints: triggers can implement more complex constraints than check constraints.

Tracking changes: triggers can detect database operations and prohibit unauthorized changes in the database.

Level-1 intermodal lines

Call stored procedures

How triggers work

 

At work, SQL Server creates two special tables for each trigger in the server's memory: insert and Delete tables.

(1) Insert a table

Once an insert operation is performed on the table, a copy of all the rows inserted to the table is stored in the inserted Table, that is, the inserted Table is used to store the inserted content of the original table.

(2) Delete a table

Once the delete operation is performed on the table, all the deleted rows are stored in the deleted table. This aims to restore the deleted rows from the deleted table once the trigger encounters a statement that forces it to stop being executed.

Create a trigger

Before creating a trigger, you must consider the following aspects:

The create trigger statement must be the first statement for batch processing.

The owner of the table has the default permission to create a trigger. The owner of the table cannot pass the permission to other users.

The trigger is a database object, so its name must comply with the naming rules.

Although you can refer to objects in other databases in the SQL statement of the trigger, the trigger can only be created in the current database.

Although triggers can refer to views or temporary tables, you cannot create triggers in views or temporary tables. You can only create triggers in base tables or tables that create views.

A trigger can only correspond to one table, which is determined by the trigger mechanism.

 

[Example] Create a trigger. When a record is inserted into Table S, the records in Table s are automatically displayed.

 

Create trigger changedisplay

On S

For insert

As

Select * from S

View triggers view triggers using system stored procedures

Exec sp_help 'trigger name'

Understand the general information of a trigger, such as the trigger name, attribute, type, and creation time.

Exec sp_helptext 'trigger name'

View the trigger body information

Exec sp_depends 'trigger name'

Exec sp_depends 'table name'

View all the triggers involved in the table referenced by the specified trigger or the specified table

Modify a trigger

Use sp_rename to modify the trigger name

Sp_rename oldname, newname

 

Delete a trigger

Use the system command drop trigger to delete a specified trigger

Drop trigger name

Note: When you delete the table where the trigger is located, SQL Server automatically deletes the trigger related to the table.