Basic syntax and functions of triggers in SQLSever, and sqlsever triggers

Basic syntax and functions of triggers in SQLSever, and sqlsever triggers

What is a trigger?

A trigger is a stored procedure that is automatically executed 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 constraints with more complex check constraints. The difference between a trigger and a common stored procedure is that a trigger is used to operate a table. For example, update, insert, and delete operations, the system automatically calls the trigger for executing the table. In SQL Server 2005, triggers can be divided into two types: DML triggers and DDL triggers. DDL triggers affect the execution of multiple data definition language statements, including create, alter, and drop statements.

SQL Server creates two special tables for each trigger: Inserted Table and Deleted table.

Triggers are usually used to force business rules.

A trigger is an advanced constraint that defines more complex constraints than a CHECK constraint: It can execute complex SQL statements (if/while/case) and reference other

Columns in the table

The trigger is defined on a specific table and related to the table.

Automatic Execution

Cannot be called directly

Is a transaction (rollback) advantage of using a trigger can be achieved through the relevant tables in the database cascade changes, however, through cascading integrity constraints can be more effective to execute these changes. A trigger can force more complex constraints than those defined by the CHECK constraint. Unlike CHECK constraints, triggers can reference columns in other tables. For example, a trigger can use the SELECT statement in another table to compare the inserted or updated data and perform other operations, such as modifying data or displaying user-defined error information. The trigger can also evaluate the table status before and after data modification and take countermeasures based on the difference. Multiple similar triggers (INSERT, UPDATE, or DELETE) in a table allow multiple different countermeasures to respond to the same modification statement.

DML triggers are divided:

1. after trigger (triggered later)

A. insert trigger

B. update trigger

C. delete trigger

UPDATE trigger creation trigger syntax

Create trigger trigger_nameON table_name [with encryption] FOR [DELETE, INSERT, UPDATE] AST-SQL statement GO

NOTE: with encryption indicates the SQL text DELETE, INSERT, and UPDATE defined by the ENCRYPTION trigger. The insert trigger example specifies the trigger type.

/* Create an insert trigger and create an insert trigger on recordInfo of the online record table */create trigger tr_insert_recordInfoon recordInfofor insertas/* to define variables, used to temporarily store the inserted member number, computer number, and card number */declare @ cardId char () declare @ PCId intdeclare @ 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 computer usage status based on the computer Number */update PCInfo set PCUse = where PCId = @ PCId/* query the member number based on the card number */select @ cardNumber = CardNumber from cardinfo Where CardId = @ cardid/* indicates the success of the Computer */print! Member number: '+ @ CardNumber +' machine number: '+ convert (char (), @ PCId) go ---- insert test data, set nocount on the machine with the member number -- do not display the number of records affected by the SQL statement declare @ CardId int --- declare the variable of the number of a memory card --- investigate the card number according to the member number select @ cardId = cardid from cardinfo where cardNumber = 'C' --- insert a record to the recordInfo table, card number, computer number, and machine time insert into recordInfo (cardId, PCId, beginTime) values (@ cardId, getDate () ---- view the result select * from recordInfoselect * from PCInfo

Update trigger example

--- Create a delete trigger and create a delete trigger on RecordInfo. create trigger tr_delete_recordInfoon recordInfofor deleteasif exists (select * from sysobjects where name = 'backrecordinfo') ---- if the backRecordInfo table ex, insert into backRecordInfo select * from deletedelse ---- create the backRecordInfo table and obtain the deleted data from deleted. select * into backRecordInfo from deletedprint 'backrecordinfo table has been backed up successfully, the data in the backup table is: 'select * from backRecordInfogo ------- key code ---------- test the delete trigger. delete the data set nocount ondelete from recordInfo --- view result print'. The data in the record table is: 'select * from recordInfo

Delete trigger example

------- Key code ------ create trigger before using updateasdeclare @ beforePCId intdeclare @ afterPCId intselect @ beforePCId = PCId from deletedselect @ afterPCId = PCID from inserted --- modify the usage status based on the computer number -------- number to change the computer usage status: update PCInfo set PCUse = where PCId = @ beforePCId --- change the computer usage status: update PCInfo set PCUse = where PCId = @ afterPCId ---- the computer has been changed. print the computer has been changed! Switch from '+ convert (varchar (), @ beforePCId) +' computer to '+ convert (varchar (), @ afterPCId) +' computer 'go/* to test the update trigger, modify the computer Number */-- display the data in the record table before 'select' from recordInfo -- display the data in the record table before the change */-- display the data in the computer table before the change, data in the computer table 'select * from PCInfoset nocount on --- change the computer number to update recordInfo set PCId = where PCId = --- view the result print, after the data 'select * from recordInfoprint 'in the record table is changed, the data in the computer table is 'select * from PCInfo

The instead of trigger can be used in both data tables and views. We recommend that you use the instead of trigger in the following situations: the data in the database cannot be modified. For example, the call records of the Telecom Department cannot be modified, the call charge count will be inaccurate. You may want to roll back the modified SQL statement to use the trigger in the view to modify the data instead of the trigger example in your own way.

--- Create an update trigger and create a modification (column) on the recordInfo) trigger create trigger restart recordInfoinstead of insertasdeclare @ cardbalance int -- declare the variable declare @ CardId int used to store the user card number -- declare the variable declare @ PCId int used to store the computer variable of the number --- the inserted record row information in the inserted temporary table, including the computer number and card number select @ cardId = cardId, @ PCId = PCId from insertedselect @ cardbalance = cardBalance from cardInfo where CardId = @ CardIdprint your balance is: '+ convert (varch Ar (10), @ cardBalance) --- print the balance information if (@ cardBalance <2) --- determine the amount of balance and check whether the balance on the machine is normal. print 'the balance is less than RMB and cannot be used on the machine. Please recharge as soon as possible! 'Else ---- change the computer usage status to using update PCInfo set PCUse = 1 where PCId = @ PCId ---- insert the computer record insert into recordInfo (cardId, PCId, beginTime) values (@ CardId, @ PCId, getdate ()) print 'computer succeeded '------- key code ------ set nocount ondeclare @ cardId int --- variable for declaring the ID of a memory card --- locate the card number based on the member number select @ cardId = cardId from cardInfo where cardNumber = 'c001' ---- insert a record to the recordInfo table, card number, computer number, and computer time insert into recordInfo (cardId, PCId, beginTime) values (@ cardId, 1, getdate () select * from recordInfoselect * from PCInfo

The above section describes the basic syntax and functions of triggers in SQLSever. I hope it will help you!