SQL trigger Instance 1
Definition: What is a trigger? In SQL Server is a certain operation of a table, triggering certain conditions, thus executing a program. A trigger is a special stored procedure.
There are three common triggers: Apply to insert, Update, and Delete events, respectively.
Why should I use triggers? For example, so two tables:
Copy Code code as follows:
Create table Student (--Student table
StudentID int PRIMARY KEY,--school number
....
)
Create table Borrowrecord (--Student library record
Borrowrecord int Identity (1,1),--Serial number
StudentID int,--School number
Borrowdate datetime,--loan time
Returndate Datetime,--return time
...
)
the features used are:
1. If I change the student's number, I hope his library record is still relevant to the student (i.e., changing the school number of the library record at the same time);
2. If the student has graduated, I would like to delete his study number while also deleting its library record.
Wait a minute.
Triggers can be used at this time. For 1, create an UPDATE trigger:
Copy Code code as follows:
Create Trigger trustudent
On Student-creating triggers in the Student table
For Update--why event triggers
As--the thing to do after the event is triggered
If Update (StudentID)
Begin
Update Borrowrecord
Set Studentid=i.studentid
From Borrowrecord BR, Deleted D, Inserted i--deleted and Inserted temporary tables
Where Br. Studentid=d.studentid
End
Understand the two temporary tables inside the trigger: Deleted, Inserted. Note that deleted and inserted respectively represent the "old record" and "new record" of the table that triggered the event.
There are two virtual tables in a database system that store information that changes in a table, respectively:
Virtual table inserted virtual table deleted
Store new records when table records are new do not store records
New records to be updated when modified
Delete records are not stored when the record is deleted
The process of an update can be seen as: Generate new records to inserted tables, copy old records to deleted tables, and then delete student records and write record entries.
For 2, create a DELETE trigger
Copy Code code as follows:
Create Trigger Trdstudent
On Student
For Delete
As
Delete Borrowrecord
From Borrowrecord BR, delted D
Where Br. Studentid=d.studentid
From these two examples we can see the key to the trigger: A.2 a temporary table; B. Trigger mechanism.
SQL trigger Instance 2
Copy Code code as follows:
/*
Set up a virtual test environment, including: Table [cigarette inventory table], table [Cigarette sales table].
Please pay attention to tracking the data of these two tables, to understand what the trigger in the end of the business logic, what impact on the data.
In order to articulate the role of triggers, the table structure has data redundancy and does not conform to the third paradigm, as described here.
*/
Use Master
Go
IF EXISTS (SELECT NAME from sysobjects WHERE XTYPE = ' U ' and NAME = ' cigarette Inventory table ')
DROP Table Cigarette Inventory table
Go
IF EXISTS (SELECT NAME from sysobjects WHERE XTYPE = ' U ' and NAME = ' cigarette Sales table ')
DROP Table cigarette Sales table
Go
--Business Rules: Sales amount = Sales quantity * Sales unit price Business rules.
CREATE Table cigarette Sales table
(
Cigarette brand VARCHAR (+) PRIMARY KEY not NULL,
Buyer VARCHAR (a) NULL,
Sales Quantity INT NULL,
Sales Unit Price NULL,
Sales Amount Money NULL
)
Go
--Business Rules: Inventory amount = Inventory quantity * Inventory unit price Business rules.
CREATE Table Cigarette Inventory table
(
Cigarette brand VARCHAR (+) PRIMARY KEY not NULL,
Inventory Quantity INT NULL,
Inventory unit Price Money NULL,
Inventory Amount Money NULL
)
Go
--Create a trigger, example 1
/*
Create TRIGGER [T_insert_ cigarette Inventory table], this trigger is simpler.
Note: The trigger is raised whenever an INSERT action occurs in the Cigarette inventory table.
Trigger function: Enforces business rules to ensure that the amount of inventory in the data being inserted = Inventory quantity * Inventory unit price.
Note: [INSERTED], [DELETED] is a system table, cannot be created, modified, deleted, but can be invoked.
Important: The structure of the two system tables is the same as the table that inserts the data.
*/
IF EXISTS (SELECT NAME from sysobjects WHERE XTYPE = ' TR ' and NAME = ' T_insert_ cigarette Inventory table ')
DROP TRIGGER t_insert_ Cigarette Inventory table
Go
CREATE TRIGGER t_insert_ Cigarette Inventory table
On cigarette Inventory table
For INSERT
As
--Commit TRANSACTION processing
BEGIN TRANSACTION
--Enforce the following statements to ensure business rules
UPDATE Cigarette Inventory Table
SET Inventory amount = Inventory quantity * Inventory Unit price
WHERE cigarette brand in (SELECT cigarette brand from INSERTED)
COMMIT TRANSACTION
Go
/*
Insert test data for the [Cigarette inventory table]:
Note that the data in the first data (Hongtashan new forces) conforms to the business rules,
The second piece of data (Hongtashan), [inventory amount] is empty, does not conform to the business rules,
In the third data (Yunnan image), [inventory amount] is not equal to [inventory quantity] multiplied by [inventory unit price] and does not conform to the business rules.
Fourth data inventory quantity is 0.
Note that after inserting the data, check that the data in the [Cigarette inventory table] is Inventory amount = Inventory quantity * Inventory unit price.
*/
INSERT into cigarette inventory table (cigarette brand, stock quantity, stock price, stock amount)
SELECT ' Hongtashan New Forces ', 100,12,1200 UNION all
SELECT ' Hongtashan artificial peaks ', 100,22,null UNION all
SELECT ' Yunnan image ', 100,60,500 UNION all
SELECT ' Yuxi ', 0,30,0
Go
--Querying data
SELECT * FROM cigarette inventory table
Go
/*
Result set
RecordID cigarette brand Inventory quantity inventory Unit Price inventory amount
-------- ------------ -------- ------- ---------
1 Hongtashan New Forces 100 12.0000 1200.0000
2 Hongtashan artificial peaks 100 22.0000 2200.0000
3 Yunnan Images 100 60.0000 6000.0000
4 Yuxi 0 30.0000.0000
(The number of rows affected is 4 rows)
*/
--trigger Example 2
/*
Create TRIGGER [T_insert_ cigarette Sales Table], this trigger is more complex.
Note: The trigger is raised whenever an INSERT action occurs in the Cigarette inventory table.
Trigger functionality: Implementing business rules.
Business rules: If the sale of the cigarette brand does not exist inventory or inventory is zero, then return an error.
Otherwise, the inventory quantity and inventory amount of the corresponding brand cigarette in the [Cigarette inventory table] will be reduced automatically.
*/
IF EXISTS (SELECT NAME from sysobjects WHERE XTYPE = ' TR ' and NAME = ' t_insert_ cigarette sales table ')
DROP TRIGGER t_insert_ Cigarette Sales table
Go
CREATE TRIGGER t_insert_ Cigarette Sales table
On cigarette sales table
For INSERT
As
BEGIN TRANSACTION
Check the legality of the data: whether the cigarettes sold are in stock or if the inventory is greater than 0
IF not EXISTS (
SELECT Inventory Quantity
From Cigarette inventory table
WHERE cigarette brand in (SELECT cigarette brand from INSERTED)
)
BEGIN
--Return error hint
RAISERROR (' Wrong! The cigarette does not exist in stock and cannot be sold. ', 16, 1)
--Rolling back transactions
ROLLBACK
Return
End
IF EXISTS (
SELECT Inventory Quantity
From Cigarette inventory table
WHERE cigarette brand in (SELECT cigarette brand from INSERTED) and
Inventory Quantity <= 0
)
BEGIN
--Return error hint
RAISERROR (' Wrong! The cigarette inventory is less than or equal to 0 and cannot be sold. ', 16, 1)
--Rolling back transactions
ROLLBACK
Return
End
--processing of legitimate data
--Enforce the following statements to ensure business rules
UPDATE Cigarette Sales Table
SET Sales Amount = Sales quantity * Sales price
WHERE cigarette brand in (SELECT cigarette brand from INSERTED)
DECLARE @ cigarette brand VARCHAR (40)
SET @ cigarette brand = (SELECT cigarette brand from INSERTED)
DECLARE @ Sales Quantity Money
SET @ Sales quantity = (SELECT sales quantity from INSERTED)
UPDATE Cigarette Inventory Table
SET Inventory quantity = Inventory Quantity-@ sales quantity,
Inventory amount = (inventory quantity-@ Sales quantity) * stock price
WHERE cigarette brand = @ cigarette Brand
COMMIT TRANSACTION
Go
--Please keep track of the data changes in the [Cigarette inventory table] and [cigarette sales table].
--Insert the first test data for the [Cigarette sales table], which is normal.
INSERT into cigarette sales table (cigarette brands, buyers, sales quantity, sales price, sales amount)
SELECT ' Hongtashan new Force ', ' a buyer ', 10,12,1200
Go
--For [cigarette sales table], insert the second test data, which is not equal to the sales unit price * Sales quantity.
-The trigger automatically corrects the data so that the sales amount equals the sales unit price * Sales quantity.
INSERT into cigarette sales table (cigarette brands, buyers, sales quantity, sales price, sales amount)
SELECT ' Hongtashan for Peak ', ' a buyer ', 10,22,2000
Go
--For [cigarette sales table], insert the third test data, the cigarette brand in the cigarette inventory table can not find the corresponding.
--the trigger will make an error.
INSERT into cigarette sales table (cigarette brands, buyers, sales quantity, sales price, sales amount)
SELECT ' Red River V8 ', ' a buyer ', 10,60,600
Go
/*
Result set
Server: Message 50000, Level 16, State 1, Process t_insert_ cigarette sales table, line 15
Error! The cigarette does not exist in stock and cannot be sold.
*/
--For [cigarette sales table], insert a third test data, the cigarette brand in the Cigarette inventory table inventory of 0.
--the trigger will make an error.
INSERT into cigarette sales table (cigarette brands, buyers, sales quantity, sales price, sales amount)
SELECT ' Yuxi ', ' One of the buyers ', 10,30,300
Go
/*
Result set
Server: Message 50000, Level 16, State 1, Process t_insert_ cigarette sales table, line 29
Error! The cigarette inventory is less than or equal to 0 and cannot be sold.
*/
--Querying data
SELECT * FROM cigarette inventory table
SELECT * FROM cigarette sales table
Go
/*
Add:
1, this example mainly through a simple business rule implementation for the use of triggers, the specific need to flexibly deal with;
2, on the trigger to understand and use good INSERTED, DELETED two system tables;
3. The triggers created by this example are all for inserts, and the specific syntax can refer to:
Trigger syntax
Copy Code code as follows:
CREATE TRIGGER trigger_name
On {table | view}
[WITH encryption]--for encryption triggers
{
{for | After | INSTEAD of} {[INSERT] [,] [UPDATE]}
[With APPEND]
[Not for REPLICATION]
As
[{IF UPDATE (column)
[{and | OR} UPDATE (column)]
[... n]
| IF (columns_updated () {bitwise_operator} updated_bitmask)
{comparison_operator} Column_bitmask [... n]
} ]
Sql_statement [... n]
}
}
4, about the trigger, you should also pay attention to
(1), DELETE Trigger cannot capture TRUNCATE TABLE statement.
(2), the following Transact-SQL statements are not allowed in triggers:
ALTER database CREATE Database DISK INIT
DISK RESIZE DROP Database LOAD Database
LOAD LOG Reconfigure RESTORE DATABASE
RESTORE LOG
(3), triggers can nest up to 32 layers.
*/
--Modifying triggers
-In essence, is the CREATE TRIGGER ... Modified to alter TRIGGER ... Can.
--Deleting triggers
DROP TRIGGER XXX
Go
--Delete test environment
DROP Table Cigarette Inventory table
Go
DROP Table cigarette Sales table
Go
DROP TRIGGER t_insert_ Cigarette Inventory table
Go
DROP TRIGGER t_insert_ Cigarette Sales table
Go
##################################################################
Basic knowledge and examples of triggers
: Create Trigger Tr_name
On Table/view
{for | after | instead of} [UPDATE] [,] [Insert] [,] [Delete]
[WITH Encryption]
As {batch | if update (col_name) [{and|or} update (col_name)]}
Description
1 Tr_name: Trigger Name
2 on Table/view: table to which triggers are acting. A trigger can only work on a table
3 for and after: synonymous
4 after and instead Of:sql 2000 new project Afrer and instead of the difference between
After
is activated only after the triggering event occurs and can only be established on the table
Instead of
is executed instead of the corresponding triggering event, either on the table or on the view
5 Insert, UPDATE, Delete: Three actions to activate a trigger, which can be executed at the same time, optionally
6 if Update (COL_NAME): Indicates whether the action has an effect on the specified column, and activates the trigger. Also, because the delete operation has only effect on rows,
So if you use the delete operation, you can't use this statement (although it doesn't make a mistake, it doesn't make sense to activate the trigger).
7 The two special tables used when the trigger executes: deleted, inserted
Deleted and inserted can be said to be a special temporary table, which is automatically generated by the system when the trigger is activated, and the structure of the table structure with the trigger function is a
Kind of, just storing the data differently.
Continued
The following table shows the differences between deleted and inserted data
The difference between deleted and inserted data
Inserted
Storing data after insert and update operations
Deleted
Data stored prior to delete and update operations
Note: The update operation is equivalent to the delete before the insert, so in the update operation, the modified data copy to the deleted table, modified
Data is stored in a table that triggers the action, and also generates a copy into the insered table
