The basic syntax and function of triggers in SQLSEVER

Time:2020-4-6

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!