SQL Server triggers

A trigger is a special type of stored procedure, which is different from the stored procedure we introduced earlier. A trigger is triggered by an event and automatically called for execution. The stored procedure can be called by the name of the stored procedure.

A trigger is a special type of stored procedure, which is different from the stored procedure we introduced earlier. A trigger is triggered by an event and automatically called for execution. 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 generally used for constraints with more complex check constraints. The difference between a trigger and a common stored procedure is that a trigger is used to operate a table. For example, update, insert, and delete operations, the system automatically calls the trigger for executing the table. In SQL Server 2005, triggers can be divided into two types: DML triggers and DDL triggers. DDL triggers affect the execution of multiple data definition language statements, including create, alter, and drop statements.

DML triggers are divided:

1. after trigger (triggered later)

A. insert trigger

B. update trigger

C. delete trigger

2. instead of trigger (previously triggered)

The after trigger requires that the trigger be triggered only after an insert, update, or delete operation is executed and can only be defined on the table. The instead of trigger does not execute the defined operations (insert, update, delete) but only executes the trigger itself. You can either define an instead of trigger on a table or a view.

The trigger has two special tables: The Insert table and the delete table ). These two tables are logical tables and virtual tables. Two tables created by the system in the memory are not stored in the database. Both tables are read-only and can only read data but cannot modify data. The results of these two tables are always the same as the structure of the table applied by the modified trigger. After the trigger is complete, the two tables will be deleted. The data in the Inserted Table is the Inserted or modified data, and the data in the deleted table is the data before or after the update.

When updating data, you delete the table record and add a record. In this way, the inserted and deleted tables have updated data records. Note: The trigger itself is a transaction, so you can perform some special checks on the modified data in the trigger. If not, you can use transaction rollback to cancel the operation.

Create a trigger

Syntax

Tgr_name
Table_name
Encrypion-encryption trigger
...

Transact-SQL

# Create an insert trigger

-- Create an insert trigger
(Object_id (,))
Tgr_classes_insert

Tgr_classes_insert
Classes
Insert -- insert trigger

-- Define variables
@ Id, @ name (20), @ temp;
-- Query inserted records in the inserted Table
@ Id = id, @ name = name inserted;
@ Name = @ name + (, @ id );
@ Temp = @ id/2;
Insert student (@ name, 18 + @ id, @ temp, @ id );
;

-- Insert data
Insert classes (, getDate ());
-- Query data
* Classes;
* Student id;

Insert trigger. A newly inserted record is added to the inserted Table.

# Create a delete trigger

-- Delete trigger
(Object_id (,))
Tgr_classes_delete

Tgr_classes_delete
Classes
-- Deletion trigger

;
(Object_id (,))
-- ClassesBackup exists to insert data directly.
Insert classesBackup name, createDate deleted;
  
-- ClassesBackup does not exist before creation and insertion
* ClassesBackup deleted;
;



-- Nocount;
Classes name =;
-- Query data
* Classes;
* ClassesBackup;

The delete trigger will save the deleted data in the deleted table When deleting the data.

# Create an update trigger

-- Update type trigger
(Object_id (,))
Tgr_classes_update

Tgr_classes_update
Classes
   

@ OldName (20), @ newName (20 );
-- Data before update
@ OldName = name deleted;
(* Student name + @ oldName + ))
    
-- Updated data
@ NewName = name inserted;
Student name = replace (name, @ oldName, @ newName) name + @ oldName +;
;
    
  
;

-- Query data
* Student id;
* Classes;
Classes name =;

After the update trigger updates the data, it saves the pre-update data in the deleted table and the updated data in the inserted Table.

# Update a column-Level Trigger

(Object_id (,))
Tgr_classes_update_column

Tgr_classes_update_column
Classes
   

-- Column-Level Trigger: whether the class creation time is updated
(CreateDate ))
  
(, 16, 11 );
;
  

-- Test
* Student id;
* Classes;
Classes createDate = getDate () id = 3;
Classes name = id = 7;

You can use update to determine whether to update Column records;

# Instead of Type trigger

The instead of trigger indicates that the operation defined by the trigger is not executed (insert, update, delete), but only the content of the trigger itself.

Create syntax

  tgr_name
 table_name
 encryption
  instead  ...

  T-SQL

# Create an instead of trigger

(Object_id (,))
Tgr_classes_inteadOf

Tgr_classes_inteadOf
Classes
Instead

@ Id, @ name (20 );
-- Query the deleted information and assign values to the disease
@ Id = id, @ name = name deleted;
+ (, @ Id) ++ @ name;
-- Delete student information first
Student cid = @ id;
-- Delete the classes information.
Classes id = @ id;
+ (, @ Id) ++ @ name +;

-- Test
* Student id;
* Classes;
Classes id = 7;

# Display custom message raiserror

 (object_id(, )   )
    tgr_message

  tgr_message
 student
   insert, 
 (, 16, 10);

--test
insert  student (, 22, 1, 7);
 student  sex = 0  name = ;
 *  student   id;

# Modify a trigger

  tgr_message
 student
 
 (, 16, 10);

--test
  student  name = ;

# Enable or disable a trigger

-- Disable a trigger
Disable tgr_message student;
-- Enable trigger
Enable tgr_message student;

# Querying created trigger information

-- Query existing triggers
* Sys. triggers;
* Sys. objects type =;

-- View trigger events
Te. * sys. trigger_events te sys. triggers t
T. object_id = te. object_id
T. parent_class = 0 t. name =;

-- View the trigger creation statement
Sp_helptext;

# Example: Verify the inserted data

 ((object_id(, )   ))
    tgr_valid_data

  tgr_valid_data
 student
 insert

   @age ,
      @name (20);
   @name = s.name, @age = s.age  inserted s;
   (@age < 18)
  
    (, 16, 1);
     ;
  

--test
insert  student (, 2, 0, 7);
insert  student (, 22, 0, 7);
 *  student   id;

# Example: Operation Log

(Object_id (,))
Log

Log (
Id (1, 1 ),
(20 ),
CreateDate datetime getDate ()
)

(* Sys. objects name = ))
Tgr_student_log

Tgr_student_log
Student
Insert ,,

(1 inserted) (1 deleted )))

Insert log ()();

(1 inserted) (1 deleted ))

Insert log ()();

(1 inserted) (1 deleted ))

Insert log ()();


-- Test
Insert student (, 22, 1, 7 );
Student sex = 0 name =;
Student name =;
* Log;
* Student id;