SQL Server triggers

This article is to learn to use trigger data summary, the content from the network, at the end of the article will give the corresponding link address.

In SQL Server R2, both T-SQL triggers are supported and CLR triggers are created.

Introduction to a Trigger

1) Trigger classification

• Login trigger: Is triggered when a user logs on to a SQL Server instance to establish a session.
• 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.
• DML (Data manipulation language manipulation Language) trigger: Refers to a trigger that is enabled when a DML event occurs in the database. DML events refer to insert, UPDATE, DELETE statements that modify data in a table or view

2) Introduction to DML triggers

• 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. 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
• References to columns in other tables, such as triggers, can 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 respond 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.

Two small examples of creating T-SQL triggers

　　　　

1) First, let's try to create a trigger that requires the creation of an update trigger on the AddTable table with the following statement:

Create trigger Mytrigger on addtable for update

2) Then there is the part of the SQL statement, mostly if the trigger triggers an action after an update occurs. This means that if an update is present, the trigger will trigger the output: The table was updated!.

The statement is: Create TRIGGER Mytrigger on addtable for update as print ' The table is updated '

3) Next we will perform a change to the data in the AddTable table with the following statement: Update addtable SET * * * *

4) After execution, we will find that the trigger is triggered and output our set of text (in the message box below)

5) After the trigger is created, it officially begins to work, and when we need to change the trigger, we just have to make the start create alter and then modify the logic: Alter TRIGGER Mytrigger on addtable for update

6) If we want to view the contents of a trigger, run it 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, only need to run: SELECT * from sysobjects where xtype= ' TR '

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

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

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

Original: http://jingyan.baidu.com/article/77b8dc7f2b82416175eab65b.html

Three-CLR triggers

It's very clear on MSDN http://msdn.microsoft.com/zh-cn/library/vstudio/938d9dz2%28v=vs.100%29.aspx

• Open an existing SQL CLR database project, or create a new project. For more information, see How to: Create a project for database objects that use SQL Server common Language runtime integration.

• on the Project menu, choose Add New Item.

• In the Add New Item dialog box, select triggers.

• Type a name for the new trigger .

• Add code to run when the trigger executes. See the first example after this procedure.

• solution Explorer, open the testscripts folder and double-click the span Class="label">test.sql file. " > Open the test.sql file.

• solution Explorer, open the testscripts folder and double-click the span Class="label">test.sql file. " > solution Explorer, open the testscripts folder and double-click the test.sql file." >test.sql file to execute The trigger. " >      Add code to the test.sql file to execute the trigger.

• f5 to build, deploy, and debug the trigger. " >    Press F5 build, deploy, and debug this trigger. how to:deploy SQL CLR Database Project Items t o a SQL Server. " > For information about how to deploy directly without debugging, see How to: Deploy a SQL CLR database project item to SQL Server.

• View the results shown in the Output window and select Display output: Database output.

Attention:SQL Server 2005 and SQL Server 2008 support only SQL Server projects that were built using the. NET Framework version 2.0, 3.0, or 3.5.If you try to deploy a SQL Server project that is SQL Server 2005 or SQL Server 2008, an error message is displayed:Deploy Error (SQL01268):. NET SqlClient Data provider:msg 6218, Level 3, line 1 CREATE ASSEMBLY for ASSEMBL Y ' AssemblyName ' failed because assembly ' AssemblyName ' failed verification. Check if the referenced assemblies is up-to-date and trusted (for external_access or unsafe) to execute in the database (InThe checksum is the name of the assembly you are deploying. For more information, see How to: Create a project for database objects that use SQL Server common Language runtime integration.

assemblyname is the name of the the assembly so you are deploying). " >how to:create a Project for Database Objects this use SQL Serv ER Common Language run-time integration. " >             1) CLR trigger code

This example shows that the user chooses any user name they want, but you want to know which users have entered the e-mail address as their user name. This trigger detects the information and logs it to the audit table.

　　　　　　Using System.Data.SqlClient;　　　　　　Using System.Text.RegularExpressions;　　　　　　Using Microsoft.SqlServer.Server;   　　　　　　 Public partial class Triggers {[Sqltrigger (name= "UserNameAudit", target= "Users", event= "for INSERT")]        　　　　　　 public static void UserNameAudit () {SqlTriggerContext triggcontext = Sqlcontext.triggercontext;        　　　　　　SqlParameter userName = new SqlParameter ("@username", System.Data.SqlDbType.NVarChar); if (triggcontext.triggeraction = = Triggeraction.insert) {using (SqlConnection conn = new Sq Lconnection ("Context connection=true")) {Conn.                　　　　　　Open ();                　　　　　　SqlCommand Sqlcomm = new SqlCommand ();                　　　　　　SqlPipe SQLP = sqlcontext.pipe;                　　　　　　Sqlcomm.connection = conn;                　　　　　　Sqlcomm.commandtext = "Select UserName from INSERTED"; Username.value = Sqlcomm.executescalar (). TostrING (); if (Isemailaddress (username.tostring ())) {Sqlcomm.commandtext = "INSERT use                   　　　　　　 Rsaudit (UserName) VALUES (UserName) ";                   　　　　　　 Sqlp.send (Sqlcomm.commandtext);                　　　　　　Sqlp.executeandsend (Sqlcomm);  }}}} public static bool Isemailaddress (string s) {return Regex.IsMatch (S, "^ (\\w-]+\\.) *? [\\w-] [Email protected] [\\w-]+\\. ([\\w-]+\\.) *?   　　　　[\\w]+$ "); }　　　　}

assemblyname is the name of the the assembly so you are deploying). " >how to:create a Project for Database Objects this use SQL Serv ER Common Language run-time integration. " >              2) test script

assemblyname is the name of the the assembly so you are deploying). " >how to:create a Project for Database Objects this use SQL Serv ER Common Language run-time integration. " >                    add code to the Test.sql file located in the project's TestScripts folder to execute and test the trigger. For example, if you have deployed a trigger, you can test it by running a script that inserts a new row into the table where the trigger is set, which fires the trigger.

              following debug generation The code assumes that there are two tables with the following definitions:

 CREATE TABLE Users (UserName NVARCHAR) is not NULL,                     Pass NVARCHAR ($) not NULL) CREATE TABLE Usersaudit (UserName NVARCHAR (+) not NULL) 
 Test Script: 

                --Insert one user name, is not a e-mail address                 and one that is Insert into Users (UserName, Pass) VALUES (N ' Someo Ne ', n ' cnffjbeq ')                 INSERT into Users (UserName, Pass) VALUES (n ' [email protected] ', n ' cnffjbeq ')                  --Check the users and Usersaudit tables to see the results of the trigger                 select * from the Users                 select * from Usersaudit

           If there is an error executing a CLR trigger in SQL Server, you need to change the configuration of the database using the SQL statement:
          exec sp_configure ' show advanced options ', ' 1 ';　　　　Go reconfigure; Go EXEC sp_configure ' clr enabled ', ' 1 '
Go
Reconfigure　　　　exec sp_configure ' show advanced options ', ' 1 '; Go

Management and viewing of four triggers

Original: http://www.cnblogs.com/shineqiujuan/archive/2009/04/23/1442137.html

1. Manage and view triggers through visual actions

In Microsoft SQL Server Management Studio, when you select a table for a database, you have a trigger item in the Object Explorer Details window.

With the "Trigger" right-click menu feature, we can create a new trigger. If a trigger already exists in the original table, you can view the specific trigger by double-clicking the trigger item, where you can perform modifications, deletions, and so on.

2. Manage and view triggers through Query Analyzer
1) View the trigger types in the table:
Sp_helptrigger: Returns the type of DML trigger defined for the specified table of the current database. Sp_helptrigger cannot be used with DDL triggers.
Example: EXEC sp_helptrigger ' table name '

2) View information about the trigger:
Sp_help: Reports information about database objects (all objects listed in the Sys.sysobjects Compatibility View), user-defined data types, or data types.
Example: EXEC sp_help ' trigger name '

3) Display the definition of the trigger:
Sp_helptext: Displays the text of a rule, default value, unencrypted stored procedure, user-defined function, trigger, or view.
Example: EXEC sp_helptext ' trigger name '

4) View all the triggers in the current library:
Query script: SELECT * from Sysobjects WHERE xtype = ' TR '

5) View all the triggers in the current library and the tables that correspond to them:
Query script: SELECT tb2.name as tablename,tb1.name as Triggername from Sysobjects Tb1 joins Sysobjects TB2 on Tb1.parent_obj=tb2.id WHERE tb1.type= ' TR

SQL Server triggers