Maintenance of SQL Server Database (ii) __ triggers

--Maintenance Database--
-- Trigger --

--Overview：
Note:a trigger is a special type of stored procedure，used to enforce business rules。 On invocation, triggers cannot be called directly by the user through T-SQL statements, as stored procedures do, but require the execution of INSERT, UPDATE, delete events that occur on the database to fire the automatic execution of the trigger. Therefore, when creating and using triggers, you need to consider the execution of the triggering event and the triggered event, and then design and create the trigger to complete the user's requirements.
triggers can enforce business rules like constraints when data in data tables and views change。 In some ways, triggers are better than constraints, because triggers can contain complex code written in the T-SQL language and can involve data from other data tables.a function trigger that can be done with constraints, but the solutions it gives are not always the best. Therefore, constraints and triggers have advantages in different situations.
As with stored procedures, statements that create triggers must exist in the same batch。
--category: According to the language classification of the triggering event, the trigger can be divided intoDML triggersAndDDL triggers。
1)DML triggers: it meanstriggers that can be triggered by INSERT, update, and delete operations on a data table。
2)DDL triggers: it meansc triggers that can be triggered by the create, alter, and drop operations of the database object。
The creation, modification, and deletion methods of the two types of triggers are similar.
--DML Trigger (Detailed description):
Note:DML triggers differ according to trigger and execution events, can be divided intoAfter type triggersAndinstead of type triggers。
♦after type trigger (POST trigger Trigger): If the event represented by the T-SQL statement encapsulated by the trigger is called a "triggered event", the event represented by the INSERT, UPDATE, or DELETE statement that triggered the trigger is called a "trigger event." The after type trigger is the trigger that "triggered event" executes when the trigger event completes successfully.
after type triggers can only be created on data tables, whilecannot create on view。 A data table can create multiple triggers that are generated by the same trigger action, but the execution order of the triggers fired by the same trigger action is random, and the trigger can be executed by means of the method settings for disabling and recovering the trigger.
♦instead of type triggers (substitution triggers): In short, the instead of type triggers when a "trigger event" occurs, the system does not perform a "trigger event" specific action (such as INSERT, UPDATE, or delete data), but instead executes the "triggered event" directly.
instead of type triggers can be defined on the table and on the view。 Only one instead of type trigger can be defined for each triggering action (INSERT, update, or delete).

--inserted table and deleted table
Note:inserted tables and deleted tables are temporary tables prepared by the system for each trigger, stored in memory。 When the trigger is triggered, the inserted table and the deleted table record each record information designed during the execution of the trigger to facilitate user use and query.the records in the inserted table and the deleted table can only be viewed, cannot be modified, and the temporary tables associated with them are deleted when the trigger finishes executing .。
Without the "updated" table, update acts as a modification, adding data to the deleted data, so that when the action is triggered as the update command, the deleted table is generated and the inserted table is generated.
♦inserted Table
　　　　 the inserted table is used to hold the data information generated when the trigger action is the Insert command.。 The structure of the table is the same as the table structure in which the Insert command works, and the information in the table is stored as a copy and only the newly occurring record information is stored.
♦deleted table
　　　　 the deleted table is used to hold the data information generated when the trigger action is the delete command.。 The structure of the table is the same as the table structure in which the Delete command works, and the information in the table is stored as a copy, and only the newly occurring record information is stored.
--Trigger Management--
--Create a trigger(Create Trigger)--
Note: In SQL Server, triggers can be created using the "organizer" and the T-SQL language two ways.executing a trigger is actually the trigger command (INSERT, update, and delete) when the trigger is defined。
CREATE TRIGGER Trigger nameThe--create trigger command represents the creation of a trigger, which requires the first sentence in a batch.
On data table name--"On data table name" refers to the data table where "trigger event" occurs.
[with encryption]The--with encryption statement can be used to create different types of triggers.
After | instead of [insert] [, UPDATE] [, delete]The--after and instead of options are available to create different types of triggers. Insert, UPDATE, delete is the operation, can be single or multiple selection, there is no sequencing.
asThe T-SQL statement after--as is "triggered event".
T-SQL statements

Example 1: (After type trigger) (a trigger named "T_ Customer Information table"), when the customer contact phone number 20130001 of the Customer information table in the Product management database is modified to 13600003333 when the operation is successfully executed, the result prints a " The record has been modified! "Prompt for information. )

--Create a trigger
Use Commodity Management database
Go
Create trigger T_ Customer Information Form _ Phone
On customer Information Form
After update--after type triggers
As
print ' record has been modified! ‘
Select*from Customer Information Form

--Execute "trigger event" firing trigger execution
Use Commodity Management database
Go
Update Customer Information Sheet
Set Contact phone = ' 13600003333 ' where customer number = ' 20130001 '
Go

Example 2: (instead of type trigger) (Create a trigger named "T_ Customer Information Table _ Phone No", when the customer contact phone number for customer information sheet in the product Management database is modified to 13600004444, no modification is performed. and print one in the results "record not modified!" "Prompt for information. )

--Create a trigger
Use Commodity Management database
Go
Create trigger T_ Customer Information Form _ Phone No
On customer Information Form
Instead of update
As
print ' Record not modified! ‘
Select*from Customer Information Form

--Execute "trigger event" firing trigger execution
Use Commodity Management database
Go
Update Customer Information Sheet
Set Contact phone = ' 13600004444 '
Where customer number = ' 20130001 '

Example 3: (After type trigger) (when a product is sold in the commodity Management database, in addition to adding a sales record to the Sales Information table, the inventory quantity that is equivalent to the quantity sold in the Inventory information table should be lost.) Create a trigger named "T_ Sales Table _ Inventory table". )
Use Commodity Management database
Go
Create Trigger T_ Sales Table _ Inventory table
On sales Information Sheet
After insert
As
Update inventory Information table
Set Inventory quantity = Inventory Quantity-(select sales quantity from inserted)

--Check the existing number of items in the "Inventory information Sheet" item number 11110003
Select*from Inventory information table where product number = ' 11110003 '
--Add a product number to the sales information sheet wei11110003 sold 10 records to excite the stored procedure.
Use Commodity Management database
Go
Insert Sales Information Sheet VALUES (8, ' 11110003 ', 2.5,10,50, ' 2012-12-21 ', 20130004)
--Check the existing quantity of the item number 11110003 in the Inventory information table, compare with Previous
Select*from Inventory information table where product number = ' 11110003 '

-- view triggers
NOTE: because triggers are special stored procedures, you can use system stored procedures to view information about triggers .
exec system stored procedure user-defined stored procedure name
--sp_depends: View trigger Dependencies
--sp_help: View the trigger's creation information
--sp_helptext: View the trigger's creation text (the encrypted trigger cannot be viewed)

EXEC sp_helptrigger data table name
--sp_helptrigger: to see which types of triggers have been created in a data table

Example: (See the "Sales Information sheet" in the Product management database, create those triggers to view the trigger information you have created)
Use Commodity Management database
Go
EXEC sp_helptrigger Sales Information sheet

Cases:
Use Commodity Management database
Go
EXEC sp_depends T_ Sales table _ Inventory table
EXEC sp_help T_ Sales table _ Inventory table
EXEC sp_helptext T_ Sales table _ Inventory table

-- modifying triggers
Note: Whether you modify a trigger name or a feature , you have an effect on the fields in the data table or datasheet that the trigger is associated with, so be careful to modify the
- similar to modifying a stored procedure, modifying a trigger is tantamount to deleting the original trigger function and creating a new trigger function .

1)
ALTER TRIGGER trigger Name
On data table name
[WITH Encryption]
After | Instead of [insert] [, UPDATE] [, delete]
As
T-SQL statements

2)
You can also rename a trigger using the system stored procedure sp_rename :
EXEC sp_rename original trigger name, new trigger name

-- enable trigger
Enable trigger trigger name on data table name

Example: (Enable the trigger in the Sales Information table, t__ Sales table, inventory table)
Use Commodity Management database
Go
Disable Trigger T__ Sales Table _ Inventory table on Sales Information table

-- disabling triggers
Disable trigger trigger name on data table name

Example: (Disabling the trigger in the Sales Information table, "t__ Sales table, Inventory table")
Use Commodity Management database
Go
Disable Trigger T__ Sales Table _ Inventory table on Sales Information table

-- Delete trigger
Drop TRIGGER Trigger Name

Cases:
Use Commodity Management database
Go
Drop Trigger T_ Sales Table _ Inventory table

Note: "--" can be seen as a description or comment text

Maintenance of SQL Server Database (ii) __ triggers