Introduction to triggersA trigger is actually a special type of stored procedure, which is a stored procedure that is automatically executed when a particular T-SQL statement is executed. In SQL Server 2005, triggers are divided into two types based on different SQL statements: DML triggers and DLL triggers.
Concepts and functions of triggersIn SQL Server 2005, two methods can be used to ensure data validity and integrity: Check and trigger ). Constraints are directly set in the data table. They can only be used for some simple functional operations, such as checking the validity and uniqueness of fields, automatically filling in the default values, and ensuring that the field data is not repeated (that is, the primary key) to ensure the integrity (foreign key) of the data table. A trigger is a special stored procedure for a data table (database). When an insert, update, or delete operation is performed on the table, the trigger is automatically activated for various complex operations. In SQL Server 2005, the trigger has a further function. When create, alter, and drop operations are performed on a data table (database), the trigger is automatically activated for execution. Some common functions of triggers are as follows: l data constraints that are more complex than constraints: triggers can implement data constraints that are more complex than constraints l check whether the SQL statements are allowed: the trigger can check whether SQL operations are permitted. For example, in the product inventory table, if you want to delete a product record, when you delete the record, the trigger can check whether the inventory quantity of the product is zero. If not, cancel the delete operation. L modify data in other data tables: When an SQL statement operates on a data table, the trigger can operate on another data table based on the operation of the SQL statement. For example, when an order is canceled, the trigger can automatically modify the product inventory table and subtract the number of canceled orders from the Order Quantity Field. L calling more stored procedures: the constraints themselves cannot call stored procedures, but the triggers themselves are a stored procedure, and stored procedures can be nested for use, therefore, triggers can also call one or more stored procedures. L send SQL mail: After the SQL statement is executed, the trigger can determine whether the modified records meet certain conditions. If this condition is met, the trigger can automatically call SQL mail to send emails. For example, after an order is paid, the logistics personnel can send an email to notify him to deliver the goods as soon as possible. L return custom error information: the constraint cannot return information, but the trigger can. For example, when a duplicate record is inserted, a specific friendly error message can be returned to the foreground application. L change the SQL statement to be operated: the trigger can modify the SQL statement to be operated. For example, the original SQL statement is to delete records in the data table, however, the records in the data table are the most important records and cannot be deleted. Therefore, the trigger may not execute this statement. L prevent data table structure changes or data tables from being deleted: To protect created data tables, triggers can not operate on data tables in SQL statements starting with drop or alter.
