SQL Server triggers

A trigger (trigger) is a method that SQL Server provides to programmers and data analysts to ensure data integrity, which is a special stored procedure related to table events, which is executed either by a program call or by a manual startup, but by an event, such as when a table is manipulated ( Insert,delete, update) will be activated when it executes. Triggers are often used to enforce data integrity constraints, business rules, and so on. Triggers can be found in the dba_triggers, user_triggers data dictionary. The SQL3 trigger is a statement that can be automatically executed by the system to modify the database.

The only difference between a trigger and a stored procedure is that the trigger cannot execute the EXECUTE statement call, but instead automatically triggers execution when the user executes the Transact-SQL statement. (Excerpt from Baidu Encyclopedia) basic syntax for triggers View the list of triggers for the current database

Select *  from where xtype='TR'

Where sysobjects holds the object of the database, where the record xtype as TR is the trigger object. Xtype Type Control

C	CHECK constraint
D	Defaults or DEFAULT Constraints
F	FOREIGN KEY Constraint
L	Log
Fn	Scalar functions
IF	Inline table functions
P	Stored Procedures
Pk	PRIMARY KEY constraint (type is K)
RF	Replication Filter Stored Procedures
S	system tables
Tf	Table functions
Tr	Trigger
U	User table
UQ	UNIQUE constraint (type is K)
V	View
X	Extended stored Procedures

Create a trigger

Create Trigger Trigger name      on Table name      for Insert Delete )as    T-SQL statement

modifying triggers

Alter Trigger Trigger name      on Table name      for Insert Delete )    as// t-SQL statement to be modified

Delete Trigger

Drop Trigger Trigger Name

View the contents of a trigger

' trigger name ' 

renaming triggers

exec sp_rename original name, new name

disabling triggers

Alter Table Trigger Trigger Name

enabling triggers

Alter Table Trigger Trigger Name

A complete instance of the trigger
When there is a data delete operation in the So_master table, the trigger records the deleted information, as well as the client host name, IP address, user name, logon time and other related information to the DELETEDBU table

Alter Triggertrideleted--Trigger name is trideleted onSo_master--the table associated with the trigger is So_master     for Delete --Delete Trigger as    Declare @info nvarchar( -)--Stitching the deleted information    Select @info=Ordnum+Linnum+Delnum fromDeleted--(the deleted table is equivalent to the column in the So_master table to be deleted)    if @info<>"'     begin        --Client host name, client IP address, current database engine user name, current logon time        Declare @ipAddress nvarchar( the),@loginName nvarchar(Ten),@hostName nvarchar(Ten),@loginTime datetime        Select  @ipAddress=Client_net_address fromSys.dm_exec_connectionswheresession_id= @ @SPID        Select @loginTime=Login_time,@hostName=HostName,@loginName=Loginame fromsysprocessesWhereSPID=@ @SPID        --inserts the information that is deleted in the So_master table, and the client-related information into the record table        Insert  intoDeletedbuValues(@info,@loginName,@hostName,@ipAddress,@loginTime)    EndGo

This article draws from: http://www.cnblogs.com/albert-struggle/archive/2011/08/10/2133665.html

SQL Server triggers