SQL2000 trigger instance

/*
Create a virtual test environment, including the table [cigarette inventory table] and table [cigarette sales table].
Please pay attention to tracking the data of these two tables and understanding what business logic the trigger has executed and what impact it has on the data.
To better express the role of a trigger, the table structure has data redundancy and does not conform to the third paradigm.
*/
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 sale table ')
Drop table cigarette sales table
Go

-- Business rule: Sales amount = sales quantity * sales unit price business rule.

Create Table cigarette sales table
(
Cigarette brand varchar (40) primary key not null,
Purchaser varchar (40) null,
Sales quantity int null,
Unit Price: Money 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 (40) primary key not null,
Inventory quantity int null,
Inventory Unit Price: Money null,
Inventory amount: Money null
)
Go

-- Create a trigger, Example 1

/*
Create a trigger [t_insert _ cigarette inventory table], which is relatively simple.
Note: This trigger is triggered whenever an insert action occurs in the [cigarette inventory table.
Trigger function: enforce business rules to ensure that the inserted data is in stock amount = inventory quantity * inventory unit price.
Note: [Inserted] and [deleted] are system tables. They cannot be created, modified, or deleted, but can be called.
Important: The two system tables have the same structure as the table that inserts 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
-- Execute 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: The data in the first data (the new power of hongtashan) conforms to the business rules,
[Inventory amount] is blank in the second data (hongtashan manfeng) and does not comply with business rules,
Article 3 In the data (Yunnan image), [inventory amount] is not equal to [inventory quantity] multiplied by [inventory unit price] and does not comply with business rules.
Article 4 The data inventory quantity is 0.
After inserting data, check whether the data in the [cigarette inventory table] is stock amount = stock quantity * stock unit price.
*/

Insert into cigarette inventory table (cigarette brand, inventory quantity, inventory unit price, inventory amount)
Select 'hongtashan neoligan', 1200, Union all
Select 'hongta mountain others', null Union all
Select 'yunnan image', 500, Union all
Select 'yuxi ', 0, 30, 0
Go

-- Query data

Select * from cigarette inventory table
Go
/*

Result set

Recordid cigarette brand inventory quantity stock unit price Stock Amount
--------------------------------------------
1 hongtashan new forces 100 12.0000 1200.0000
2 Hongta mountain 100 22.0000 2200.0000
3. Yunnan image 100 60.0000 6000.0000
4 Yuxi 0 30.0000. 0000

(The number of affected rows is 4)

*/

-- Trigger Example 2

/*
Create a trigger [t_insert _ cigarette sales table], which is complex.
Note: This trigger is triggered whenever an insert action occurs in the [cigarette inventory table.
Trigger function: implements business rules.
Business rule: if the brand of cigarettes sold does not exist or the inventory is zero, an error is returned.
Otherwise, the inventory quantity and amount of cigarettes of the corresponding brand in the [cigarette inventory table] will be automatically reduced.
*/
If exists (Select name from sysobjects where xtype = 'tr' and name = 't_ insert _ ')
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 validity of the data: whether the cigarettes sold have inventory or whether the inventory is greater than zero
If not exists (
Select inventory quantity
From cigarette inventory table
Where cigarette brand in (select cigarette brand from inserted)
)
Begin
-- Return Error Message
Raiserror ('error! The cigarette does not exist in stock and cannot be sold. ', 16,1)
-- Roll back the transaction
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 Message
Raiserror ('error! The cigarette inventory is smaller than or equal to 0 and cannot be sold. ', 16,1)
-- Roll back the transaction
Rollback
Return
End

-- Process valid data

-- Execute the following statements to ensure business rules
Update cigarette sales table
Set sales amount = sales quantity * sales unit 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 volume money
Set @ sales volume = (select sales volume from inserted)

Update cigarette inventory table
Set inventory quantity = inventory quantity-@ sales quantity,
Inventory amount = (inventory quantity-@ sales quantity) * Inventory Unit Price
Where cigarette brand = @ cigarette brand
Commit transaction
Go

-- Keep track of the data changes in the [cigarette inventory table] and [cigarette sales table.
-- For the [cigarette sales table], insert the first test data, which is normal.

Insert into cigarette sales table (cigarette brand, purchaser, sales quantity, sales unit price, sales amount)
Select 'hongtashan neolive', 'a commodity quota', 1200
Go

-- For the [cigarette sales table], insert the second test data. The sales amount of this data is not equal to the sales unit price * sales quantity.
-- The trigger will automatically correct the data so that the sales amount is equal to the sales unit price * sales quantity.

Insert into cigarette sales table (cigarette brand, purchaser, sales quantity, sales unit price, sales amount)
Select 'hongta mountain others', 'a buyer ', 2000
Go

-- For the [cigarette sales table], insert the third test data. The cigarette brands in the data cannot be found in the cigarette inventory table.
-- Trigger will report an error.

Insert into cigarette sales table (cigarette brand, purchaser, sales quantity, sales unit price, sales amount)
Select 'honghe v8', 'buyer ', 10, 60, 600
Go

/*
Result set
Server: Message 50000, level 16, status 1, process t_insert _ cigarette sales table, row 15
Error! The cigarette does not exist in stock and cannot be sold.
*/

-- For the [cigarette sales table], insert the third test data, in which the inventory of cigarette brands is 0 in the cigarette inventory table.
-- Trigger will report an error.

Insert into cigarette sales table (cigarette brand, purchaser, sales quantity, sales unit price, sales amount)
Select 'yuxi ', 'a buyer', 10, 30, 300
Go

/*
Result set
Server: Message 50000, level 16, status 1, process t_insert _ cigarette sales table, row 29
Error! The cigarette inventory is smaller than or equal to 0 and cannot be sold.
*/
-- Query data
Select * from cigarette inventory table

Select * from cigarette sales table
Go

/*
Supplement:
1. This example describes how to use a trigger based on a simple business rule;
2. understand and use the inserted and deleted system tables for triggers;
3. All triggers created in this example are for insert. For the specific syntax, see:

Trigger syntax

Create trigger trigger_name
On {table | view}
[With encryption] -- used 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. Pay attention to the trigger.
(1) The delete trigger cannot capture the truncate TABLE statement.
(2) The following 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) A trigger can be nested with up to 32 layers.

*/

-- Modify a trigger
-- Modify create trigger... to alter trigger.

-- Delete a trigger
Drop trigger xxx
Go

-- Delete the 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 trigger knowledge and Examples
: 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)]}

Note:
1 tr_name: trigger name
2 On Table/view: the table to which the trigger applies. A trigger can only act on one table
3 For and after: Synonymous
4 after and instead of: SQL 2000 new project afrer and instead of difference
After
It is activated only after the trigger event occurs. It can only be created on a table.
Instead
Instead of triggering the event, it can be created on a table or view.
5. Insert, update, and delete: three operations for activating a trigger can be performed simultaneously.
6 if Update (col_name): indicates whether the operation has an impact on the specified column. If so, the trigger is activated. In addition, because the delete operation only affects rows,
Therefore, if you use the delete operation, you cannot use this statement (although there is no error in use, it does not make sense to activate the trigger ).
7. Two Special Tables Used for trigger execution: deleted and inserted
Deleted and inserted are a special temporary table that is automatically generated by the system when the trigger is activated. Its structure and the table structure of the trigger are
Only the data stored is different.

Continued
The following table describes the differences between deleted and inserted data.
Differences between deleted and inserted data
Inserted
Store data after insert and update operations
Deleted
Store data before the delete and update operations
Note: The update operation is equivalent to performing the delete operation before the insert operation. Therefore, when performing the update operation, copy the data before the modification to the deleted table. After the modification
When the data is stored in the table to which the trigger acts, a copy is also generated to the insered table.

Reference from: