Mssqlserver trigger details (1/4)

Definition: What is a trigger? In SQL Server, certain operations on a table trigger certain conditions to execute a program. A trigger is a special stored procedure.

There are three common triggers: Insert, Update, and Delete events.

Why should I use a trigger? For example, two tables:

     

The code is as follows:	Copy code
Create Table Student (-- Student Table StudentID int primary key, -- student ID ....        ) Create Table BorrowRecord (-- student borrow record Table BorrowRecord int identity (1, 1), -- serial number StudentID int, -- student ID BorrowDate datetime, -- lending time ReturnDAte Datetime, -- return time ...       )

The following functions are used:

1. If I change the student's student ID, I hope that his borrowing record will still be related to this student (that is, changing the student ID of the borrowing record at the same time );

2. If the student has graduated, I want to delete his student ID and his/her borrowing history.

And so on.

A trigger can be used at this time. For 1, create an Update trigger:

   

The code is as follows:	Copy code
Create Trigger truStudent On Student -- create a trigger in the Student table For Update -- why events are triggered As-what to do after an 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 in the trigger: Deleted and Inserted. Note: Deleted and Inserted indicate the "old record" and "new record" tables that trigger the event respectively ".

There are two virtual tables in a database system that are used to store the changes recorded in the table:

Virtual Table Inserted virtual table Deleted

New records are stored when table records are added. New records are not stored.

New record used for update stored during modification

The deleted records are not stored during deletion.

An Update process can be viewed as: generate a new record to the Inserted table, copy the old record to the Deleted table, delete the Student record, and write a new record.

For 2, create a Delete trigger

   

The code is as follows:	Copy code
Create trigger trdStudent On Student For Delete As Delete BorrowRecord From BorrowRecord br, Delted d Where br. StudentID = d. StudentID

From the two examples, we can see the key of the trigger: A.2 temporary tables; B. Trigger mechanism.

SQL trigger instance 2

/*

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.

*/

The code is as follows:

Copy code

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

The code is as follows:	Copy code
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:

The code is as follows:	Copy code
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

The code is as follows:	Copy code
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

:

The code is as follows:	Copy code
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 ).

Homepage 1 2 3 4 Last page