Mssqlserver trigger details (1/4)

Source: Internet
Author: User
Tags datetime mssqlserver one table rollback create database

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.