SQL Server triggers detailed _mssql

A trigger (trigger) is a method that SQL Server provides to programmers and data analysts to ensure data integrity. It is a special stored procedure associated with a table event, and its execution is not invoked by the program or initiated manually, but by events, such as when an action is performed on a table ( Insert,delete, update) is activated when it is executed.

Triggers are often used to enforce data integrity constraints and business rules. Triggers can be found in dba_triggers, user_triggers data dictionaries. A SQL3 trigger 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 used primarily to enforce compliance with complex business rules or requirements. For example, you can control whether new orders are allowed to be inserted based on the current account status of the customer.

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

the only difference between a trigger and a stored procedure is that a trigger cannot execute an EXECUTE statement call, but instead automatically triggers execution when the user executes a Transact-SQL statement.

To query all triggers in the database:

SELECT * from sysobjects where xtype= ' TR '

1. Grammar

Create trigger [Shema_name.] Trg_name on
{table | view}
[with encryption]
{for | | after | instead of}
   {INSERT, UPDATE, delete}
as
sql_statement

Insert Trigger Instance

Create trigger Test on
al for
Insert as
declare @id int, @uid int, @lid int, @result char
SELECT @ Id=id, @uid =uid, @lid =lid, @result =result from inserted
if (@lid =4)
begin
 Update al set uid=99 where id=@
 Auto-Modify User ID ' end when ID print ' lid=4

Update Trigger Instance

Create Trigger Test_update on
al for
 update as
 declare @oldid int, @olduid int, @oldlid int, @newid int, @newuid int, @newlid int
 Select @oldid =id, @olduid =uid, @oldlid =lid from deleted;
 Select @newid =id, @newuid =uid, @newlid =lid from inserted
 if (@newlid > @oldlid)
 begin
 print ' Newlid >oldid '
 rollback tran;
 End
 Else
 print ' modified successfully '

Delete Trigger Instance

Create Trigger Test_delete on
al for
Delete as
declare @did int, @duid int, @dlid int
Select @did =id, @duid =uid, @dlid =lid from deleted
if (exists (SELECT * from list where @dlid =id))
begin
print ' cannot be deleted '
Rollback Tran;
End
Else
print ' Delete succeeded '

Text Introduction triggers

Database Running Environment SqlServer2005

A trigger (trigger) is a special stored procedure that is not executed by a program call or 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 and business rules. In fact, to simply say, is the trigger is a switch, responsible for the light and the lights out, you move, it on the light, on this meaning.

Classification of triggers

1 DML (Data manipulation language manipulation Language) trigger: Refers to triggers that are 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: is enabled when a DDL event occurs in a server or database. A DDL event refers to a create, alter, DROP statement in a table or index.

3 Landing trigger: is triggered when a user logs on to a SQL Server instance to establish a session.

DML triggers introduction

1 in SQL SERVER 2008, the implementation of a DML trigger uses two logical tables deleted and inserted. These two tables are built into the database server's memory, 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 triggers are completed, they are automatically deleted: The insered table is used to store records that you update after you insert, UPDATE, and DELETE statements. For example, if you insert a piece of data, you insert the record into the inserted table: the deleted table is used to store your database in the trigger table before you manipulate the INSERT, UPDATE, and DELETE statements.

2 triggers can implement cascading changes through related tables in the database to enforce constraints that are more complex than those defined by a check constraint. Unlike CHECK constraints, triggers can refer to columns in other tables, such as triggers can use a SELECT from another table to compare data that is inserted or updated, and to perform other actions. Triggers can also take action based on the state of the table before and after the data is modified. Multiple similar triggers (INSERT, UPDATE, or DELETE) in one table allow multiple different countermeasures to respond to the same modification statement.

3 at the same time, although the trigger powerful, easy and reliable implementation of many complex functions, why should be cautious to use? Too many triggers can make database and application maintenance difficult, while the excessive reliance on triggers will affect the structure of the database, and also increase the maintenance of complex programs.

Trigger steps

1 First, let's try to create a trigger that requires you to create an update trigger on the addtable table:

Create Trigger Mytrigger on addtable
For update

2 is then part of the SQL statement, primarily if update is required to trigger a trigger action. The idea here is that if update is present, the trigger triggers the output: The table was updated!---by Piglet.

3 Next we'll do a change to the data in the AddTable table:

4 after execution, we will find that the trigger is triggered, outputting the text we set up:

5 After the trigger is created, it's officially started, and when we need to change the trigger, we just have to convert the start create creation into alter, and then we can modify the logic:

6 If we want to view the contents of a trigger, run directly: EXEC sp_helptext [trigger name]

7 If I want to query how many triggers are in the current database to facilitate my database maintenance, I just need to run:

SELECT * from sysobjects where xtype= ' TR '

8 If we need to close or turn on the trigger, we only need to run:

Disable trigger [trigger name] on database--disable triggers

Enable trigger [trigger name] on database--open trigger

9 The function of the trigger is large, but once triggered, recovery is more troublesome, then we need to protect the data, here needs to use the rollback data roll back ~

10 The meaning of the nineth step is to query addtable table, if there are tablename=newtable, the data roll back, trigger aborted, then we do a test, the addtable table to make changes, found that triggers the update trigger, because there is data protection , Trigger aborted:

Attention matters

Disabling and opening triggers requires a certain amount of permissions, which cannot be manipulated if the permissions are not sufficient.

Note that the Run-time error prompts are helpful for correcting errors.