SQL trigger examples explained-PHP开发-php.cn

Definition: What is a trigger? In SQL Server, it is a program that performs a certain operation on a certain table, triggers a certain condition, and is executed. A trigger is a special stored procedure.
There are three common triggers: applied to Insert, Update, and Delete events respectively.

Why should I use triggers? For example, these two tables:

      Create Table Student(              --学生表 
        StudentID int primary key,       --学号 
        .... 
       )

Copy after login

      Create Table BorrowRecord(               --学生借书记录表 
        BorrowRecord   int identity(1,1),       --流水号   
        StudentID      int ,                    --学号 
        BorrowDate     datetime,                --借出时间 
        ReturnDAte     Datetime,                --归还时间 
        ... 
      )

Copy after login

The functions used are:
1. If I change the student's student ID, I hope that his borrowing record will still be related to the student (that is, the student ID of the borrowing record table will be changed at the same time);
2. If The student has graduated. I want to delete his student ID number and also delete his book borrowing records.
Wait.

You can use triggers at this time. For 1, create an Update trigger:

Create Trigger truStudent
On Student – Create a trigger in the Student table
for Update by )
BEGIN p Update BORROWRECORD
Set Studentid = I.Studentid
From BORROWRECORD BR, Deleted D, Inserted I-Deleted and Inserted Temporary Tables
ENTID d END

Understand the trigger Two temporary tables: Deleted and 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 used to store the information recorded in the table, which are:
. Records are not stored
When modified Store new records used for updating. Store records before updating.
: Generate new records to the Inserted table and copy old records to the Deleted table. Then delete the Student record and write a new record.

For 2, create a Delete trigger
Create trigger trdStudent
On Student
for Delete
As
From BorrowRecord br , Delted d
Where br.StudentID=d.StudentID

From these two examples, we can see the key to triggers: A. 2 temporary tables; B. Trigger mechanism.

SQL trigger instance 2

/*
Establish a virtual test environment, including: table [cigarette inventory table], table [cigarette sales table].
Please pay attention to tracking the data of these two tables and understand what business logic the trigger executes and what impact it has on the data.
In order to express the role of triggers more clearly, the table structure has data redundancy and does not conform to the third normal form. This is hereby explained.
*/
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(40) PRIMARY KEY NOT NULL,
Buyer VARCHAR(40) NULL,
Sales Quantity INT NULL,
Sales 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 trigger, Example 1

/*
Create trigger [T_INSERT_cigarette inventory table], this trigger is relatively simple.
Description: Whenever an INSERT action occurs in [Cigarette Inventory Table], this trigger is triggered.
Trigger function: Enforce business rules to ensure that in the inserted data, inventory amount = inventory quantity * inventory unit price.
Note: [INSERTED] and [DELETED] are system tables and cannot be created, modified, or deleted, but they can be called.
Important: The structure of these two system tables is the same as the structure of the table where data is inserted.
*/
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 cigarettes Inventory table
FOR INSERT
AS
--Submit transaction processing
BEGIN TRANSACTION
--Enforce the execution of the following statements to ensure the business rules
UPDATE Cigarette inventory table
SET Inventory amount = Inventory quantity * Inventory unit price
WHERE Cigarette brand IN (SELECT Cigarettes Brand from INSERTED)
COMMIT TRANSACTION
GO

/*
Insert test data for [cigarette inventory table]:
Note that the data in the first data (Hongtashan New Force) complies with the business rules,
The second data ( In Hongta Mountain (Human Peak), the [Inventory Amount] is empty, which does not comply with the business rules.
In the third piece of data (Yunnan Image), the [Inventory Amount] is not equal to [Inventory Quantity] multiplied by [Inventory Unit Price], which does not comply with the business rules.
The fourth data inventory quantity is 0.
Please note that after inserting the data, check whether the data in [Cigarette Inventory Table] is Inventory Amount = Inventory Quantity * Inventory Unit Price.
*/

INSERT INTO Cigarette inventory table (cigarette brand, inventory quantity, inventory unit price, inventory amount)
SELECT ‘Hongtashan New Force’,100,12,1200 UNION ALL
SELECT ‘Hongtashan Artificial Peak’,100,22, NULL UNION ALL
SELECT 'Yunnan Image',100,60,500 UNION ALL
SELECT 'Yuxi',0,30,0
GO

--Query data

SELECT * FROM Cigarette inventory table
GO
/*

results Set

RecordId Cigarette Brand Inventory Quantity Inventory Unit Price Inventory Amount
-------- ---------------- -------- ------- -- -------
1 Hongta Mountain New Force 100 12.0000 1200.0000
2 Hongta Mountain Artificial Peak 100 22.0000 2200.0000
3 Yunnan Image 100 60.0000 6000.0000
4 Yuxi 0 30.000 0 .0000

(The number of rows affected is 4 rows)

*/

--Trigger example 2

/*
Create a trigger [T_INSERT_Cigarette Sales Table], which is more complex.
Description: Whenever an INSERT action occurs in [Cigarette Inventory Table], this trigger is fired.
Trigger function: Implement business rules.
Business rules: If the cigarette brand sold does not exist in stock or the stock is zero, an error will be returned.
Otherwise, the inventory quantity and amount of the corresponding brand of cigarettes in the [Cigarette Inventory Table] will be automatically reduced.
*/
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 cigarettes Sales table
FOR INSERT
AS
BEGIN TRANSACTION
--Check the legality of the data: whether the cigarettes sold are in stock, 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)
--Rollback transaction
ROLLBACK
RETURN
END SIF Exists The cigarette inventory is less than or equal to 0 and cannot be sold. ',16,1)
--Rollback transaction
ROLLBACK
RETURN
END

--Process legal data

--Enforce the execution of 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 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) *Inventory unit price
WHERE cigarette brand = @cigarette brand
COMMIT TRANSACTION
GO

--Please track the data changes of [Cigarette Inventory Table] and [Cigarette Sales Table] by yourself.
--For [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 New Force', 'a purchaser', 10, 12, 1200
GO

-- For [Cigarette Sales Table], insert the second piece of test data. The data Sales Amount is not equal to Sales Unit Price * Sales Quantity.
--The trigger will automatically correct the data so that Sales Amount equals Sales Unit Price * Sales Quantity.

INSERT INTO Cigarette sales table (cigarette brand, purchaser, sales quantity, sales unit price, sales amount)
SELECT 'Hongtashan Renfeng', 'a purchaser', 10,22,2000
GO

-- For [Cigarette Sales Table], insert the third piece of test data. The cigarette brands in this data cannot be found in the cigarette inventory table.
--The trigger will report an error.

INSERT INTO Cigarette sales table (cigarette brand, purchaser, sales quantity, sales unit price, sales amount)
SELECT 'Honghe V8','a purchaser',10,60,600
GO

/*
Result set
Server: Message 50000, Level 16, State 1, Process T_INSERT_Cigarette Sales Table, Row 15
Error! The cigarettes are not in stock and cannot be sold.
*/

--For [Cigarette Sales Table], insert the third piece of test data. The cigarette brand in this data has an inventory of 0 in the cigarette inventory table.
--The trigger will report an error.

INSERT INTO Cigarette sales table (cigarette brand, purchaser, sales quantity, sales unit price, sales amount)
SELECT 'Yuxi','a purchaser',10,30,300
GO

/*
Result set
Server: Message 50000, Level 16, State 1, Process T_INSERT_Cigarette Sales Table, Row 29
Error! The cigarette inventory is less 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 mainly explains the use of triggers through the implementation of a simple business rule. The specific details should be handled flexibly according to needs;
2. About triggers It is necessary to understand and use the two system tables of INSERTED and DELETED;
3. The triggers created in this example are all FOR INSERT. For specific syntax, please refer to:
///////////////// ///////////////////////////////////////////////////// ///////////////////////////////////////////////////// /////////////

//////////////////////////////////////////////////// ///////////////////////////////////////////////////// ////////////////////////////

CREATE TRIGGER trigger_name
ON { table | view }
[ WITH ENCRYPTION ] -- used for encryption Trigger
{
{ { 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_state ment [...n]
}
}

4. Regarding triggers, you should also note that
(1), DELETE triggers cannot capture TRUNCATE TABLE statements.
(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), trigger Can be nested at most 32 floors.

*/

--Modify trigger
--In essence, just change CREATE TRIGGER... to ALTER TRIGGER....

--Delete trigger
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 List
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: The table that the trigger acts on. A trigger can only act on one table
3 for and after: synonyms
4 after and instead of: the difference between sql 2000 new items afrer and instead of
After
is activated after the trigger event occurs, and can only be created On the table
Instead of
is executed instead of the corresponding trigger event, which can be established on the table or on the view
5 insert, update, delete: the three operations of activating the trigger can be executed at the same time, or Optional one
6 if update (col_name): Indicates whether the operation has an impact on the specified column. If it does, activate the trigger. In addition, because the delete operation only affects rows,
so this statement cannot be used if the delete operation is used (although there is no error in using it, the trigger cannot be activated, which is meaningless).
7 Two special tables used when triggers are executed: deleted, inserted
deleted and inserted can be said to be a special temporary table, which is automatically generated by the system when activating a trigger. Its structure and trigger function The table structure is the same, but the data stored is different.

Continued
The following table explains the difference between deleted and inserted data
The difference between deleted and inserted data
Inserted
Stores the data after insert and update operations
Deleted
Stores the data before delete and update operations
Note: update operations are equivalent Since delete is performed first and then insert is performed, when performing the update operation, the data before modification is copied to the deleted table. While the modified data is saved to the table where the trigger is applied, a copy is also generated to the inserted table.中

/////////

CREATE TRIGGER [TRIGGER admixture_receive_log] ON dbo.chl_lydj
FOR UPDATE
AS
begin
        declare @djsfxg char(10)      declare @wtbh char(20)
        select @wtbh=wtbh from inserted
       update ly_tzk set djsfxg='已修改' where wtbh=@wtbh
end
if (select data_sfjl from t_logsetup)='是'
begin
declare @oldcjmc char (100)          declare @oldlyrq datetime
declare @oldbzbh char (60)             declare @oldzl char (20)
declare @olddj char (10)

declare @newcjmc char (100) declare @newlyrq datetime
declare @newbzbh char (60) declare @newzl char (20)
declare @newdj char (10)

declare @xgr char (20)

             select @oldcjmc=cjmc,@oldlyrq=lyrq,@oldbzbh=bzbh,@oldzl=zl,@olddj=dj from deleted
             select @newcjmc=cjmc,@newlyrq=lyrq,@newbzbh=bzbh,@newzl=zl,@newdj=dj from inserted
             select @xgr=xgr from t_modifyuser where @wtbh=wtbh

         if @oldcjmc<>@newcjmc
             begin
                      insert into t_modifylog (wtbh, mod_time, mod_table, mod_field, ori_value, now_value, mod_people) values
                      (@wtbh,getdate(), 'chl_lydj','cjmc', @oldcjmc, @newcjmc, @xgr)
             end

end
//////////修改时，直接把‘create’改为‘alter’即可

/////////////////////////

CREATE TRIGGER [TRIGGER ly_tzk_syf] ON dbo.ly_tzk
FOR insert
AS
begin
         declare @clmc char(100)     declare @dwbh char(100) declare @syf char(100)   declare @dwgcbh char(100) declare @wtbh char(50)
         declare @dj_1 money     declare @feiyong_z money   declare @feiyong_xf money   declare @feiyong_sy money
         declare @dj char(20)
        select @wtbh=wtbh , @clmc=clmc , @dwbh=dwbh ,@syf=syf from inserted
        select   @dj=dj from feihao_bz where clmc=@clmc
        select @feiyong_z=feiyong_z, @feiyong_xf=feiyong_xf, @feiyong_sy=feiyong_sy from gongchengxinxi where dwgcbh=@dwbh

        set @dj_1=convert(money ,@dj)
       if @dj_1 <>0
       begin
             set @feiyong_xf=@feiyong_xf+@dj_1
             set @feiyong_sy=@feiyong_sy-@dj_1

            update ly_tzk set syf=@dj where wtbh=@wtbh
            update gongchengxinxi set feiyong_xf=@feiyong_xf, feiyong_sy=@feiyong_sy where dwgcbh=@dwbh
       end
       else    update ly_tzk set syf=convert(char , 0.0) where wtbh=@wtbh

end

//////////////////////

CREATE TRIGGER [TRIGGER ly_tzk_syf_shanchu] ON dbo.ly_tzk
FOR delete
AS
begin
         declare @clmc char(100)     declare @dwbh char(100)    declare @dwgcbh char(100) declare @wtbh char(50)
         declare @feiyong_z money    declare @feiyong_xf money   declare @feiyong_sy money
         declare @syf char(100)      declare @syf_1 money
         --declare @dj char(20)   declare @dj_1 money
        select @wtbh=wtbh , @clmc=clmc , @dwbh=dwbh ,@syf=syf from inserted
        --select   @dj=dj from feihao_bz where clmc=@clmc
        select @feiyong_z=feiyong_z, @feiyong_xf=feiyong_xf, @feiyong_sy=feiyong_sy from gongchengxinxi where dwgcbh=@dwbh

        set @syf_1=convert(money ,@syf)
       if @syf_1 <>0
       begin
             set @feiyong_xf=@feiyong_xf-@syf_1
             set @feiyong_sy=@feiyong_sy+@syf_1
            update gongchengxinxi set feiyong_xf=@feiyong_xf, feiyong_sy=@feiyong_sy where dwgcbh=@dwbh
       end
end