Shulou(Shulou.com)05/31 Report--

This article will explain in detail what triggers are in the SQL Server database, and the content of the article is of high quality, so the editor will share it for you as a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

I. the advantages of triggers

1. The trigger is automatic. It is activated immediately after any changes are made to the data in the table.

two。 Triggers can be cascaded through related tables in the database.

3. Triggers can enforce restrictions. These limitations are more complex than those defined with CHECK constraints. Unlike CHECK constraints, triggers can reference columns in other tables.

II. Classification of triggers

SqlServer includes three general types of triggers: DML trigger, DDL trigger, and login trigger.

1. DML (data manipulation language, Data Manipulation Language) trigger

DML triggers are action code attached to a particular table or view that is performed when a data manipulation language event occurs in the database server. There are three types of DML triggers in SqlServer:

Insert trigger: triggered when data is inserted into a table

Delete trigger: triggered when data is deleted from a table

Update trigger: triggered when the data in the table is modified.

Consider using DML triggers when you encounter the following situations:

Cascading changes through related tables in the database

Prevents malicious or erroneous insert, update, and delete operations, and enforces other restrictions that are more complex as defined by check constraints.

Evaluate the status of the table before and after the data is modified, and take action according to the difference.

2. DDL (data definition language, Data Definition Language) trigger

DDL triggers are activated when data definition language (mainly statements that begin with create,drop,alter) events occur in the server or database. Using DDL triggers can prevent certain changes to the data schema or record changes or event actions in the data.

3. Login trigger

The login trigger fires the stored procedure in response to the LOGIN event. This event is raised when a user session is established with the SQL Server instance. The login trigger fires after the authentication phase of the login is complete and before the user session is actually established. Therefore, all messages from within the trigger that typically reach the user, such as error messages and messages from PRINT statements, are delivered to the SQL Server error log. If authentication fails, the login trigger is not fired.

Third, the working principle of the trigger

[DELETE, INSERT, UPDATE] corresponds to being triggered when the delete, insert and update actions are performed.

When insert is executed, the inserted data is obtained and can be read by select*from inserted.

When executing delete, get the data before deletion, which can be read by select*from deleted.

When update is executed, the data before update can be obtained and can be read by select*from deleted, and the data after update can be read by select*from inserted.

Fourth, create a trigger

1. Syntax for creating triggers:

CREATE TRIGGER trigger_name ON table_name [WITH ENCRYPTION] FOR | AFTER | INSTEAD OF [DELETE, INSERT, UPDATE] AS T-SQL statement GO-- with encryption indicates the sql text defined by the encrypted trigger-- delete,insert,update specifies the trigger type

two。 Create an insert trigger

-- create insert trigger create trigger trig_insert on student after insert as begin if object_id to determine whether the student_ sum table exists create table student_sum (stuCount int default (0));-- create the student_ sum table declare @ stuNumber int; select = count (*) from student; if not exists (select * from student_sum)-- determine whether there is a record insert into student_sum values (0) in the table. Update student_sum set stuCount = @ stuNumber;-insert the total number of updated students into the student_sum table end-the test trigger trig_insert-- > function is to insert data into the student while cascading into the student_ sum table to update the stuCount-- because it is a post-trigger, the trigger trig_insert; insert into student (stu_name,stu_gender,stu_age) values ('Lv Bu', 'male', 30) is triggered after the data is inserted first. Total number of select stuCount students from student_sum; insert into student (stu_name,stu_gender,stu_age) values ('Diao Chan', 'female' 30); total number of select stuCount students from student_sum; insert into student (stu_name,stu_gender,stu_age) values ('Cao Amiao', 'male' 40); total number of select stuCount students from student_sum

3. Create a delete trigger

When the user performs the delete operation, the delete trigger will be activated, thus controlling that the user can delete the data record from the database. After the delete trigger is triggered, the record deleted by the user will be added to the deleted table, and the corresponding record of the original table will be deleted, so view the deleted record in the deleted table.

-- create delete trigger create trigger trig_delete on student after delete as begin select stu_id as deleted student number, stu_name stu_gender,stu_age from deleted end;-- execute a delete statement to trigger trig_delete trigger delete from student where stu_id=1

4. Create a UPDATE trigger

Update triggers are called when the user executes the update statement on the specified table. This type of trigger is used to constrain the user's modification of the data. Update triggers can perform two operations: the record before the update is stored in the deleted table, and the updated record is stored in the inserted table.

-- create update trigger create trigger trig_update on student after update as begin declare @ stuCount int; select @ stuCount=count (*) from student; update student_sum set stuCount= @ stuCount Student number before select stu_id as update, student name before stu_name as update, student name after from deleted select stu_id as update, student name from inserted end after stu_name as update-created, execute a update statement to trigger trig_update trigger update student set stu_name=' Zhang Fei 'where stu_id=2

5. Management trigger

1. View trigger

(1)。 View all triggers in the database

-- View all the triggers in the database use database name go select * from sysobjects where xtype='TR'

Sysobjects holds the objects of the database, and the record in which xtype is TR is the trigger object. In the name column, we can see the trigger name.

(2). Sp_helptext view trigger content

Use database name go exec sp_helptext 'trigger name'

The contents of the trigger are displayed in the style of the table.

In addition to triggers, sp_helptext can also display rules, default values, unencrypted stored procedures, user-defined functions, and view text.

(3). Sp_helptrigger is used to view the properties of a trigger

Sp_helptrigger has two parameters: the first parameter is the table name; the second is the trigger type, char (6) type, which can be INSERT, UPDATE, DELETE, and, if omitted, displays the properties of all types of triggers in the specified table.

Use database name go exec sp_helptrigger tableName

two。 Disable enable trigger

Disable: alter table table name disable trigger trigger name

Enable: alter table table name enable trigger trigger name

If there are multiple triggers, the trigger names are separated by commas in English.

If you replace the trigger name with ALL, all triggers for the table are disabled or enabled.

3. Modify trigger

Modify trigger syntax ALTER TRIGGER trigger_name ON table_name [WITH ENCRYPTION] FOR {[DELETE] [,] [INSERT] [,] [UPDATE]} AS sql_statement

4. Delete trigger

-- Syntax format: DROP TRIGGER {trigger} [,... n] Parameter: trigger: the name of the trigger to be deleted n: indicates that the placeholder of multiple triggers can be deleted. I hope the above can be helpful and learn more about which triggers are shared in the SQL Server database. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.