SQL Server triggers

A trigger is a special type of stored procedure that differs from the stored procedure we described earlier. Triggers are triggered primarily by an event that is automatically invoked to execute. The stored procedure can be called by the name of the stored procedure.

Ø what is a trigger

A special stored procedure that is automatically executed when a trigger inserts, updates, or deletes a table. Triggers are typically used on more complex constraints of check constraints. The difference between a trigger and a normal stored procedure is that the trigger is when you manipulate a table. such as: UPDATE, INSERT, delete these operations, the system will automatically invoke the execution of the corresponding trigger on the table. Triggers in SQL Server 2005 can fall into two categories: DML triggers and DDL triggers, where DDL triggers are fired that affect a variety of data definition language statements that have create, alter, and DROP statements.

DML triggers are divided into:

1. After trigger (trigger later)

A, insert trigger

B, UPDATE trigger

C, delete trigger

2. Instead of trigger (previously triggered)

Where after triggers require that triggers be triggered only after an operation is executed, INSERT, UPDATE, delete, and can only be defined on the table. The instead of trigger indicates that the action (INSERT, UPDATE, DELETE) does not execute its definition, but only the execution of the trigger itself. You can define instead OF triggers on a table or on a view.

The trigger has two special tables: Insert Table (instered table) and delete table (deleted table). Both of these are logical tables and virtual tables. There are two tables that are created by the system in memory and are not stored in the database. Both tables are read-only and can only read data and not modify the data. The result of these two tables is always the same as the structure of the table to which the trigger was modified. When the trigger finishes working, the two tables are deleted. The data of the inserted table is inserted or modified, and the data of the deleted table is the data before or after the update.

Operations on a table	Inserted logic table	Deleted logic Table
Add record (insert)	Storage of Added Records	No
Delete a record (delete)	No	To store deleted records
Modify record (update)	Storage of updated records	Store pre-update records

When you update the data, you delete the table records and then add a record. This allows the inserted and deleted tables to have updated data records. Note that the trigger itself is a transaction, so you can make some special checks on the modified data in the trigger. If the transaction can be rolled back, the undo operation is not satisfied.

Ø CREATE Trigger

Grammar

Create Trigger Tgr_name  on table_name  with encrypion– encryption Trigger      for Update ...  as     Transact-SQL

# Create Insert Type Trigger

--Create insert Insert type triggerif(object_id('Tgr_classes_insert','TR') is  not NULL)    Drop TriggerTgr_classes_insertGoCreate TriggerTgr_classes_insert onClasses for Insert --Insert Trigger as    --Defining Variables    Declare @id int,@name varchar( -),@temp int; --query in the inserted table for the record information already inserted    Select @id =Id@name =Name frominserted; Set @name = @name + Convert(varchar,@id); Set @temp = @id / 2; Insert  intoStudentValues(@name, - + @id,@temp,@id); Print 'Add Student Success! ';Go--Inserting DataInsert  intoClassesValues('Class 5',getDate());--Querying DataSelect *  fromclasses;Select *  fromStudentOrder  byId

Insert trigger, a newly inserted record is added to the inserted table.

# Create a Delete type trigger

--Delete Remove type triggerif(object_id('Tgr_classes_delete','TR') is  not NULL)    Drop TriggerTgr_classes_deleteGoCreate TriggerTgr_classes_delete onClasses for Delete --Delete Trigger as    Print 'back up data ...'; if(object_id('Classesbackup','U') is  not NULL)        --presence classesbackup, inserting data directly        Insert  intoClassesbackupSelectName, CreateDate fromdeleted; Else        --does not exist classesbackup create and reinsert        Select *  intoClassesbackup fromdeleted; Print 'backup data is successful! ';Go----do not show the number of affected rows--set NOCOUNT on;DeleteClasseswhereName= 'Class 5';--Querying DataSelect *  fromclasses;Select *  fromClassesbackup;

The delete trigger saves the data that you just deleted in the deleted table when you delete the data.

# Create an update type trigger

--Update type Triggerif(object_id('tgr_classes_update','TR') is  not NULL)    Drop Triggertgr_classes_updateGoCreate Triggertgr_classes_update onClasses for Update as    Declare @oldName varchar( -),@newName varchar( -); --Pre-update data    Select @oldName =Name fromdeleted; if(exists(Select *  fromStudentwhereName like '%'+ @oldName + '%'))        begin            --Updated Data            Select @newName =Name frominserted; UpdateStudentSetName= Replace(Name,@oldName,@newName)whereName like '%'+ @oldName + '%'; Print 'Cascade Modify Data Successfully! '; End    Else        Print 'no need to modify the student table! ';Go--Querying DataSelect *  fromStudentOrder  byID;Select *  fromclasses;UpdateClassesSetName= 'class Five' whereName= 'Class 5';

The update trigger will save the updated data in the deleted table after updating the data, and the updated data will be saved in the inserted table.

# Update Updates column-level triggers

if(object_id('Tgr_classes_update_column','TR') is  not NULL)    Drop TriggerTgr_classes_update_columnGoCreate TriggerTgr_classes_update_column onClasses for Update as    --column-level triggers: Whether class creation time has been updated    if(Update(createdate))begin        RaisError('system prompts: class creation time can not be modified! ', -, One); rollback Tran; EndGo--TestSelect *  fromStudentOrder  byID;Select *  fromclasses;UpdateClassesSetCreateDate= getDate()whereId= 3;UpdateClassesSetName= 'Class Four' whereId= 7;

Update column level triggers can update column records with update;

SQL Server triggers