SQL Server triggers

Trigger

Introduction to Triggers:

a trigger (trigger) is a special stored procedure that is executed either by the program or not by hand, but by an event, which is activated when a table is manipulated (insert,delete, update) , triggers are often used to enforce data integrity constraints, business rules, and so on. In my opinion, the trigger is actually an event, just like in C #, clicking a button triggers the corresponding action.

categories of triggers:

(1) DML ( Data Manipulation Language manipulation Language) Trigger: Refers to a trigger that is enabled when a DML event occurs in the database. A DML event refers to an INSERT, UPDATE, DELETE statement that modifies data in a table or view. (2) DDL ( data Definition language Language) Trigger: Refers to when the server or database occurs (DDL events are enabled.) DDL events Refer to the Create, alter, and DROP statements in a table or index. (3) Login trigger: Is triggered when a user logs on to a SQL Server instance to establish a session.

DML triggers are most commonly used, and are divided into the following two scenarios depending on how the DML triggers are triggered:

(1) After Trigger: It performs a trigger operation after an INSERT, UPDATE, DELETE statement operation.       It is primarily used to record post-change processing or inspection and, in the event of an error, you can roll back and forth this fastener with the ROLLBACK TRANSACTION statement, but you cannot define an after trigger on the view. (2) INSTEAD of triggers: it executes the actions defined by the trigger itself before performing an insert, UPDATE, DELETE statement operation. The instead of triggers can be defined on the view.

inserted and deleted

In SQL SERVER 2008, the implementation of a DML trigger uses two logical tables deleted and inserted. These two tables are built in the memory of the database server and we have only read-only permissions. The structure of the deleted and insered tables is the same as the data table in which the triggers are located. When the trigger executes, they are automatically deleted: The insered table is used to store the records that you updated after the INSERT, UPDATE, DELETE statements. For example, if you insert a piece of data, the record is inserted into the inserted table: the deleted table is used to store the database in the trigger table before you manipulate the INSERT, UPDATE, and DELETE statements. For example, there are three data in your original table, so he also has three data. That is, we can use these two temporary memory-resident tables to test the effects of some data modifications and to set conditions for trigger actions.

pros and cons of triggers:

Triggers can implement cascading changes through related tables in the database, forcing more complex constraints than constraints defined with a CHECK constraint. Unlike CHECK constraints, triggers can reference columns in other tables, such as triggers that use SELECT from another table to compare the data that is inserted or updated, and to perform other operations. Triggers can also be based on the table state before and after the data is modified, and then take countermeasures. Multiple similar triggers (INSERT, UPDATE, or DELETE) in a table allow multiple different actions to be taken in response to the same modification statement.

At the same time, although the trigger is powerful, easy and reliable to implement many complex functions, why should be used with caution? Too many triggers can make the database and application maintenance difficult, and the excessive dependence on the trigger will inevitably affect the structure of the database, and add the complex procedures of maintenance.

Grammar

Creating triggers

1 CREATE TRIGGERTrigger Name2  onTable name3{ for |After|INSTEAD of } 4{[INSERT] [ , ] [DELETE] [ , ] 5    [UPDATE] }6  as 7SQL statements[. .. n] 

to delete a trigger:

1 DROP TRIGGER []

To Modify a trigger:

1 ALTER TRIGGERTrigger Name2  onTable name3{ for |After|INSTEAD of } 4{[INSERT] [ , ] [DELETE] [ , ] 5    [UPDATE] }6  as 7SQL statements[. .. n] 

turn on and off:

1 Trigger  on Database -- Disabling triggers 2 Trigger  on Database -- Turn on Trigger

Reminders and protection:

1 Print ' the trigger was removed * * * ' 2 RAISERROR (' data consistency verification ',1) 3 rollback Transaction

Example

Create an update trigger in the S table:

1 Create Trigger tri_updates 2  on s 3  for Update 4  as 5 Print ' The table S was updated '

prohibit deletion of records of students who fail grades in SC tables:

1 CREATE TRIGGERTri_del_grade2    onSc for DELETE3    as4     IF EXISTS(SELECT *  fromDELETED5           WHEREGrade<  -)6       ROLLBACK 

It is forbidden to change the grade of the failing student in SC to pass:

1 Create TriggerTri_update_grade2  onSc for Update3  as 4   if Update(grade)5   if exists(Select *  frominserted,deleted6   whereInserted.sno=Deleted.sno andInserted.grade>= -  andDeleted.grade< -)7   begin    8     Print 'You can't change your grades to pass.'9   rollbackTen   End

SQL Server triggers