What is a trigger?
Triggers are stored procedures that are executed automatically when a table is inserted, updated, or deleted. A special stored procedure that is automatically executed when a trigger inserts, updates, or deletes a table. Triggers are generally used for more complex check constraints. The difference between a trigger and a normal stored procedure is that a trigger is used to operate on a table. For example: update, insert, delete, the system will automatically call and execute the corresponding trigger on the table. In SQL Server 2005, triggers can be divided into two types: DML triggers and DDL triggers. DDL triggers can affect multiple data definition language statements, such as create, alter and drop statements.
SQL server creates two private tables for each trigger: the inserted table and the deleted table.
Triggers are often used to enforce business rules
Trigger is a kind of advanced constraint, which can define more complex constraints than check constraint: it can execute complex SQL statements (if / while / case), and it can reference other constraints
Column in table
Triggers are defined on specific tables and are related to tables.
Auto trigger execution
Cannot call directly
It’s a transaction (fallback) the advantage of using triggers triggers is that triggers can cascade changes through related tables in the database, but these changes can be executed more effectively through cascading referential integrity constraints. Triggers can enforce more complex constraints than those defined with check constraints. Unlike check constraints, triggers can refer to columns in other tables. For example, a trigger can use select in another table to compare inserted or updated data and perform other operations, such as modifying data or displaying user-defined error messages. Triggers can also evaluate the state of tables before and after data modification, and take countermeasures according to their differences. Multiple triggers of the same type (insert, update, or delete) in a table allow multiple different countermeasures to be taken in response to the same modification statement.
DML triggers are divided into:
1. After trigger (triggered after)
a. Insert trigger
b. Update trigger
c. Delete trigger
Update trigger create trigger syntax
CREATE TRIGGER trigger_name ON table_name [WITH ENCRYPTION] FOR [DELETE, INSERT, UPDATE] AS T-SQL statement GO
Note: with encryption indicates the SQL text defined by encryption trigger delete, insert, update specifies the type of trigger insert trigger example
/*Create the insert trigger on the online record table recordinfo*/ create trigger tr_insert_recordInfo on recordInfo for insert as /*Define variables to temporarily store the inserted member number, computer number and card number*/ declare @cardId char() declare @PCId int declare @CardNumber char() /*Obtain the information of the inserted record row from the inserted temporary table, including the computer number and card number*/ select @PCId=PCId,@cardId=CardId from inserted /*Modify the use status of the computer according to the computer number*/ update PCInfo set PCUse= where [email protected] /*Check the membership number according to the card number*/ select @CardNumber=CardNumber from cardinfo where [email protected] /*Display the success information*/ Print 'on the computer successfully! Member No.: '+ @ cardnumber +' machine No.: '+ convert (char(), @ pcid) go ----Insert test data, Member No Set NOCOUNT on -- do not display the number of record lines affected by SQL statement Declare @ cardid int --- a variable that declares the number of a memory card ---Check the card number according to the member number select @cardId=cardid from cardinfo where cardNumber='c' ---Insert a piece of record information into the recordinfo table, including the card number, computer number and time of operation insert into recordInfo(cardId,PCId,beginTime) values(@cardId,,getDate()) ----View results select * from recordInfo select * from PCInfo
Update trigger example
---Create a delete trigger on the online record table recordinfo create trigger tr_delete_recordInfo on recordInfo for delete as if exists(select * from sysobjects where name='backRecordInfo') ----If the backrecordinfo table exists, just add a record insert into backRecordInfo select * from deleted else ----Create the backrecordinfo table to get the deleted data from the deleted select * into backRecordInfo from deleted The backup data of print'backrecordinfo table succeeded. The data in the backup table is: ' select * from backRecordInfo go -------Key code------ ----Test delete trigger, delete data set nocount on delete from recordInfo ---View results Print 'the data in the record table is:' select * from recordInfo
Delete trigger example
-------Key code------ create trigger tr_update_recordInfo on recordInfo for update as declare @beforePCId int declare @afterPCId int select @beforePCId =PCId from deleted select @afterPCId=PCID from inserted ---Modify the use status according to the computer number----- ---Change the use status of the computer to: update PCInfo set PCUse= where [email protected] ---Change the use status of the computer to: update PCInfo set PCUse= where [email protected] ----Display the success of computer replacement Print 'successfully replaced! From computer '+ convert (varchar(), @ beforepcid) +' to computer '+ convert (varchar(), @ afterpcid) +' go /*Test the update trigger and change the computer number*/ --Record the data in the table before displaying changes Print 'record data in table before change' select * from recordInfo --Display data in the computer table before changes Print 'data in computer table before change' select * from PCInfo set nocount on ---Change the computer number to update recordInfo set PCId= where PCId= ---View results Print 'after change, record the data in the table' select * from recordInfo Print 'data in computer table after change' select * from PCInfo
Use of instead of triggers the scope of use of instead of triggers, which can be used in both data tables and views. In the following cases, it is recommended to use the instead of trigger: data in the database cannot be modified: for example, the call record of the telecommunication department cannot be modified, once modified, the call cost count will be inaccurate. It is possible to roll back the modified SQL statement and use the trigger in the view to modify the data in its own way instead of the trigger example
---Create update trigger and modify (column) trigger on the online record table recordinfo create trigger tr_updateColum1_recordInfo on recordInfo instead of insert as Declare @ cardbalance int -- declare the variable used to store the user balance Declare @ cardid int -- declare the variable used to store the number of the user card Declare @ pcid int -- declare the variable used to store the computer number ---Obtain the inserted record row information in the inserted temporary table, including computer number and card number select @cardId=cardId,@PCId=PCId from inserted select @cardbalance=cardBalance from cardInfo where [email protected] Print 'your balance is:' + convert (varchar (10), @ cardbalance) --- print balance information If (@ cardbalance < 2) --- judge the balance and see if it can be used normally The balance of print 'is less than yuan, so it cannot be used on the computer. Please recharge as soon as possible! ' else ----Change the use status of the computer to in use according to the number of the computer update PCInfo set PCUse=1 where [email protected] ----Inserting a record from the recordinfo table insert into recordInfo(cardId,PCId,beginTime)values(@CardId,@PCId,getdate()) Print 'successfully launched' -------Key code------ set nocount on Declare @ cardid int --- a variable that declares the number of a memory card ---Find out the card number according to the membership number select @cardId=cardId from cardInfo where cardNumber='c001' ----Insert a record information into the recordinfo table, including the card number, the computer number and the time when the computer was started insert into recordInfo(cardId,PCId,beginTime)values(@cardId,1,getdate()) select * from recordInfo select * from PCInfo
The above is a brief introduction to the basic syntax and function of triggers in SQLSEVER. I hope it can help you!