The basic grammar and function of triggers in Sqlsever _mssql

What is a trigger?

Triggers are stored procedures that are automatically executed when an INSERT, update, or delete operation is made to a table. A special stored procedure that is automatically executed when a trigger inserts, updates, and deletes a table. Triggers are generally used for more complex constraints on check constraints. The difference between a trigger and a normal stored procedure is that a trigger is an operation on a table. such as: UPDATE, INSERT, delete these operations, the system will automatically invoke the execution of the corresponding trigger on the table. Triggers in SQL Server 2005 can be grouped into two categories: DML triggers and DDL triggers, where DDL triggers affect multiple data definition language statements that have create, alter, and DROP statements.

SQL Server creates two dedicated table:inserted tables and deleted tables for each trigger.

Triggers are often used to enforce business rules

A trigger is an advanced constraint that can define a constraint that is more complex than a check constraint: a complex SQL statement (if/while/case) can be executed, and a reference to another

Columns in a table

Triggers are defined on a particular table and are related to tables.

Automatic Trigger execution

Cannot directly call

is a transaction (rollback) The benefits of using triggers the triggers can implement cascading changes through related tables in the database, although these changes can be performed more effectively through cascading referential integrity constraints. Triggers can enforce constraints that are more complex than those defined by a check constraint. Unlike CHECK constraints, triggers can refer to columns in other tables. For example, a trigger can use a SELECT from another table to compare data that is inserted or updated, and to perform other actions, such as modifying data or displaying user-defined error messages. Triggers can also evaluate the state of the table before and after the data is modified and take countermeasures based on its differences. Multiple similar triggers (INSERT, UPDATE, or DELETE) in one table allow multiple different countermeasures to respond to the same modification statement.

DML triggers are divided into:

1, after trigger (after trigger)

A, insert trigger

B, update triggers

C, delete trigger

UPDATE triggers create a triggered syntax

CREATE TRIGGER trigger_name
on table_name
[with encryption]
for [DELETE, INSERT, UPDATE]
as T-SQL statement go

Note: The WITH encryption represents the SQL text DELETE for the cryptographic trigger definition, INSERT, update specifies the type of trigger insert trigger sample

/* Create insert trigger, create insertion trigger on Internet record Table RecordInfo * * *
trigger Tr_insert_recordinfo on
recordinfo for
insert
AS/* Defines variables for temporary storage of inserted member numbers, computer numbers, and card numbers/
declare @cardId char ()
declare @PCId int
DECLARE @ Cardnumber char ()/
* Gets the information of the inserted record line from the inserted temporary table, including the computer's number, the card's number/
select @PCId =pcid, @cardId =cardid from Inserted/
* According to the computer number to modify the use of the status of the computer/
update pcinfo set pcuse= where pcid= @PCId
* * According to the number of cards to inquire member number * * *
Cardnumber=cardnumber from Cardinfo where cardid= @cardid/
* Display successful information on machine/
print ' on machine success! The membership number is: ' + @CardNumber + ' machine number is: ' +convert (char (), @PCId
)
go----Insert test data, membership number is on machine
set NOCOUNT ON-- Do not show the number of rows affected by the SQL statement
declare @CardId int---Declare a variable for the number of a memory card
---
select @cardId =cardid from Cardinfo where cardnumber= ' C ' 
---Inserts a record message into the RecordInfo table, the card number, the computer number, and the on-machine time
insert INTO RecordInfo (cardid,pcid , begintime) VALUES (@cardId,, GetDate ())
----View results
SELECT * FROM RecordInfo
select * from Pcinfo

UPDATE triggers sample

---Create a delete trigger, create a deletion trigger on the Internet record table RecordInfo
trigger Tr_delete_recordinfo on
recordinfo for
Delete
as if exists (select * from sysobjects where name= ' backrecordinfo ')
----Add a record
if the Backrecordinfo table exists INSERT INTO Backrecordinfo SELECT * Deleted
else
----create backrecordinfo table, get deleted data select from deleted
* Into the backrecordinfo from deleted
print ' Backrecordinfo table the backup data was successful and the data in the backup table is: '
select * from Backrecordinfo
Go
-------Key code------
----Test the Delete trigger, delete the data set NOCOUNT on the delete from
recordinfo
---View the results
the data in the print ' record table is: '
select * from RecordInfo

Delete Trigger sample

-------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---according to computer
Number modification using state--------Change the computer's use status to: Update Pcinfo set pcuse= where pcid= @beforePCId---based on the computer number you use now, change the computer's use status to: Update Pcinfo set pcuse= where pcid= @afterPCId----show that the computer was successfully changed print ' Machine success! From ' +convert (varchar (), @beforePCId) + ' computer to the ' +convert (varchar (), @afterPCId) + ' computer ' GO/* Test UPDATE trigger, modify the computer number/--before showing changes, 
Data in the record table ' before changing, the data in the Record table ' select * FROM RecordInfo--before displaying the change, data in the computer table ' before changing the data in the Computer table ' SELECT * Pcinfo SET NOCOUNT ON ---to change the computer number to update recordinfo set pcid= where pcid=---View result print ' changes, the number in the computer table after the data ' select * from RecordInfo print ' changes in the record table According to the ' select * from Pcinfo 

Instead OF triggers use instead of the use of triggers, instead OF triggers can be used in both data tables and views. It is generally recommended that you use the instead OF triggers: The data in the database is not modified: for example, telephone records in the telecommunications department cannot be modified, and once modified, the count of call charges will be inaccurate. It is possible to rollback a modified SQL statement to use a trigger in the view to modify the data in its own way instead OF triggers example

---Create an update trigger, create a modified (column) trigger on the Internet record table RecordInfo trigger Tr_updatecolum1_recordinfo on RecordInfo instead of insert as DECLARE @cardbalance INT--Declaring variables to store user balances declare @CardId INT--Declaring a variable declare to store the number of the user card @PCId INT--Declaring a variable to store the computer number---insert Ed temp table Gets the inserted record line information, including the computer number, card number select @cardId =cardid, @PCId =pcid from inserted select @cardbalance =cardbalance from Cardinfo where cardid= @CardId print ' Your balance is: ' +convert (varchar), @cardBalance)---Print balance information if (@cardBalance <2)--- Judge the balance of how much, see whether normal on the machine print ' balance is less than yuan, can not be on the machine. Please recharge as soon as possible! ' Else----Modify the computer's usage status according to the computer's number to change the use of the update pcinfo set pcuse=1 where pcid= @PCId----Insert the machine record into the RecordInfo table inserts into Recordinf O (cardid,pcid,begintime) VALUES (@CardId, @PCId, GETDATE ()) print ' On-machine success '-------critical code------SET NOCOUNT ON declare @cardId int---A variable that declares the number of a memory card---Identify the number of the card based on the member number select @cardId =cardid from Cardinfo where cardnumber= ' c001 '----
Inserts a record information into the RecordInfo table, the number of the card, the number of the computer, and the machine time insert into RecordInfo (cardid,pcid,begintime) VALUES (@cardId, 1,getdate ()) SELECT * FROM RecordInfo select * from PcinFo 

The above is a small set to introduce the trigger in the sqlsever of the basic grammar and function of the relevant content, I hope to help you!