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.

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

1. Grammar

Create Trigger Tgr_name
On table_name
With encrypion– encryption trigger
    For update ...
As
    Transact-SQL

2. Create Insert Type Trigger

--Create Insert Insert type Trigger
if (object_id (' Tgr_classes_insert ', ' tr ') is not null)
    Drop Trigger Tgr_classes_insert
Go
Create Trigger Tgr_classes_insert
On classes
    For insert--insert Trigger
As
    --Defining variables
    declare @id int, @name varchar (), @temp int;
    --In the inserted table, the query has inserted record information
    Select @id = id, @name = name from inserted;
    Set @name = @name + convert (varchar, @id);
    Set @temp = @id/2;    
    INSERT into student values (@name, @id, @temp, @id);
    print ' Add Student success! ‘;
Go
--Inserting data
Insert into classes values (' Class 5 ', GetDate ());
--Querying data
SELECT * from Classes;
SELECT * FROM student the order by ID;

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

3. Create a Delete type trigger

--delete Delete Type Trigger
if (object_id (' Tgr_classes_delete ', ' TR ') is not null)
    Drop Trigger Tgr_classes_delete
Go
Create Trigger Tgr_classes_delete
On classes
    For Delete--delete trigger
As
    print ' backup data ... ';    
    if (object_id (' classesbackup ', ' U ') is not null)
        --presence of classesbackup, direct insertion of data
        INSERT into Classesbackup select name, createdate from deleted;
    Else
        --There is no classesbackup create and reinsert
        SELECT * into Classesbackup from deleted;
    print ' backup data is successful! ‘;
Go
--
--does not show the number of affected rows
--set nocount on;
Delete classes WHERE name = ' 5 class ';
--Querying data
SELECT * from Classes;
SELECT * from Classesbackup;

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

4. Create an update type trigger

--update Update Type Trigger
if (object_id (' tgr_classes_update ', ' TR ') is not null)
    Drop Trigger Tgr_classes_update
Go
Create Trigger Tgr_classes_update
On classes
    For update
As
    DECLARE @oldName varchar (), @newName varchar (20);
    --Pre-update data
    Select @oldName = name from deleted;
    if (exists (SELECT * from student where name like '% ' + @oldName + '% '))
        Begin
            --Updated data
            Select @newName = name from inserted;
            Update student Set name = replace (name, @oldName, @newName) where name like '% ' + @oldName + '% ';
            PRINT ' Cascade modified data successfully! ‘;
        End
    Else
        print ' No need to modify the student table! ‘;
Go
--Querying data
SELECT * FROM student the order by ID;
SELECT * from Classes;
Update classes Set name = ' Five class ' WHERE name = ' 5 class ';

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.

5. Update column-level triggers

if (object_id (' Tgr_classes_update_column ', ' TR ') is not null)
    Drop Trigger Tgr_classes_update_column
Go
Create Trigger Tgr_classes_update_column
On classes
    For update
As
    --Column-level triggers: Whether class creation time has been updated
    if (update (createdate))
    Begin
        RaisError (' System Tip: Class creation time cannot be modified! ', 16, 11);
        Rollback Tran;
    End
Go
--Test
SELECT * FROM student the order by ID;
SELECT * from Classes;
Update classes Set createdate = GetDate () where id = 3;
Update classes Set name = ' Class Four ' where id = 7;

Update column level triggers can update column records with update;

6. Instead of type trigger

Instead of triggers indicate that the operation (INSERT, UPDATE, delete) is not executed, but only the contents of the trigger itself.

Create syntax

Create Trigger Tgr_name
On table_name
With encryption
    Instead of update ...
As
    T-SQL

7. Create instead OF triggers

if (object_id (' tgr_classes_inteadof ', ' TR ') is not null)
    Drop Trigger Tgr_classes_inteadof
Go
Create Trigger Tgr_classes_inteadof
On classes
    Instead of delete/*, update, insert*/
As
    declare @id int, @name varchar (20);
    --Query for deleted information, sickness assignment value
    Select @id = id, @name = name from deleted;
    print ' ID: ' + convert (varchar, @id) + ', Name: ' + @name;
    --Delete student's information first
    Delete student WHERE cid = @id;
    --Delete the classes information again
    Delete classes where id = @id;
    print ' Delete [ID: ' + convert ' (varchar, @id) + ', Name: ' + @name + '] Information succeeded! ‘;
Go
--test
SELECT * FROM student the order by ID;
SELECT * from Classes;
Delete classes where id = 7;

# Show custom Message RAISERROR

if (object_id (' tgr_message ', ' TR ') is not null)
    Drop Trigger Tgr_message
Go
Create Trigger Tgr_message
On student
    After insert, update
As RaisError (' Tgr_message trigger is triggered ', 16, 10);
Go
--test
INSERT into student values (' Lily ', 22, 1, 7);
Update student Set sex = 0 where name = ' Lucy ';
SELECT * FROM student the order by ID;

8. Modify the Trigger

Alter TRIGGER Tgr_message
On student
After delete
As RaisError (' Tgr_message trigger is triggered ', 16, 10);
Go
--test
Delete from student where name = ' Lucy ';

9. Enable and disable triggers

--Disabling triggers
Disable trigger tgr_message on student;
--Enable Trigger
Enable trigger tgr_message on student;

10. Trigger information for query creation

--Query for triggers that already exist
SELECT * from Sys.triggers;
SELECT * from sys.objects where type = ' TR ';

--View Trigger trigger events
Select te.* from sys.trigger_events te join Sys.triggers t
On t.object_id = te.object_id
where T.parent_class = 0 and T.name = ' tgr_valid_data ';

--View CREATE TRIGGER statement
exec sp_helptext ' tgr_message ';

# example, validating insert data

if ((object_id (' Tgr_valid_data ', ' TR ') is not null))
    Drop Trigger Tgr_valid_data
Go
Create Trigger Tgr_valid_data
On student
After insert
As
    DECLARE @age int,
            @name varchar (20);
    Select @name = s.name, @age = s.age from inserted s;
    if (@age < 18)
    Begin
        RaisError (' Age of inserting new data ' is problematic ', 16, 1);
        Rollback Tran;
    End
Go
--test
INSERT into student values (' Forest ', 2, 0, 7);
INSERT into student values (' Forest ', 22, 0, 7);
SELECT * FROM student the order by ID;

# example, action log

if (object_id (' Log ', ' U ') is not null)
drop TABLE Log
Go
CREATE TABLE log (
ID int Identity (1, 1) primary key,
Action varchar (20),
CreateDate datetime default GetDate ()
)
Go
if (exists (SELECT * from sys.objects where name = ' Tgr_student_log '))
Drop Trigger Tgr_student_log
Go
Create Trigger Tgr_student_log
On student
After insert, UPDATE, delete
As
if ((Exists (select 1 from inserted) and (exists (select 1 from deleted))
Begin
Insert into log values (' updated ');
End
else if (exists (select 1 from inserted) and not exists (select 1 from deleted))
Begin
Insert into log values (' inserted ');
End
else if (not exists (select 1 from inserted) and exists (select 1 from deleted))
Begin
Insert into log values (' deleted ');
End
Go
--test
INSERT into student values (' King ', 22, 1, 7);
Update student Set sex = 0 where name = ' King ';
Delete student where name = ' King ';
select * from log;
SELECT * FROM student the order by ID;

SQL Server triggers