Sql Server-触发器入门

1.触发器

①含义：

　　触发器为特殊类型的存储过程，可在执行SQL语句事件时自动生效。

②分类：

　　SQL Server 包括三种常规类型的触发器：DML 触发器、DDL 触发器和登录触发器。

③创建语法：

IF OBJECT_ID (N'触发器名称', N'TR') IS NOT NULL
    DROP TRIGGER 触发器名称
GO
create trigger 触发器名称
on 对应的操作表
for INSERT、UPDATE 或 DELETE
as
    T-SQL
go

④常用触发器操作：

-- 1.查询当前数据库下的触发器
select * from sysobjects where xtype='TR'
go

--2.删除触发器：drop trigger --多个已逗号隔开
drop trigger 触发器名称
go

-- 3.重命名触发器名称
exec sp_rename 原名称, 新名称
go

-- 4.禁用触发器：alter table 表名 disable trigger 触发器名称
alter table 表名 disable trigger all --禁用所有触发器
alter table 表名 disable trigger 触发器名称 --禁用指定触发器，多个以逗号相隔

-- 5.启用：alter table 表名 enable trigger 触发器名称  
alter table 表名 enable trigger all --禁用所有触发器
alter table 表名 enable trigger 触发器名称 --禁用指定触发器，多个以逗号相隔

-- 6.查看触发器内容
exec sp_helptext '触发器名称'

2.DML触发器

类别	简写	含义
AFTER（FOR）	for	用触发器里面的SQL语句替换需要执行的操作，例如Delete
INSTEAD OF	无	待SQL执行完后才触发触发器中的T-SQL语句

 

 

 

 

3.触发器操作：insert、update、delete

　-- CREATE TABLE

IF OBJECT_ID (N'Product') IS NOT NULL
    DROP TABLE Product
GO
-- 创建物品信息
CREATE TABLE Product(
productId INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
productName VARCHAR(30) NOT NULL,
productCordNo VARCHAR(50) NOT NULL,
productPrice decimal(10,2) NOT NULL,
createtime_product datetime NOT NULL default(getdate()),
last_updatetime datetime NOT NULL
)
GO

-- 创建收费单,关联物品信息ID
IF OBJECT_ID(N'chargeorder') IS NOT NULL
    DROP TABLE chargeorder
GO
CREATE TABLE chargeorder (
chargeorderId INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
chargeorderPrice decimal(10,2) NOT NULL, --实收物品售价
Number INT CHECK(Number>0), -- 支付数量
Amount as(chargeorderPrice*Number) persisted, --计算列：实收金额
paytime datetime NOT NULL DEFAULT(GETDATE()),-- 收费单支付时间
createtime_chargeorder datetime NOT NULL DEFAULT(GETDATE()), --创建时间
last_updatetime_chargeorder datetime ,--最后更新时间
FK_ProductId INT FOREIGN KEY REFERENCES Product(productId) --创建外键约束
)
GO

IF OBJECT_ID(N'chargeorderdeteli') IS NOT NULL
    DROP TABLE chargeorderdeteli
GO
-- 创建收费单详情，关联收费单ID
CREATE TABLE chargeorderdeteli(
chargeorderdeteliId INT NOT NULL  IDENTITY(1,1) PRIMARY KEY,
chargeorderdeteliName VARCHAR(50) NOT NULL, --对应商品名称
chargeorderdeteliPyType INT NOT NULL DEFAULT('0'), --对应支付类型
chargeorderdeteliPySee INT NOT NULL, -- 账单支付人
createtime_chargeorderdeteli DATETIME NOT NULL DEFAULT(GETDATE()) ,-- 创建时间
last_updatetime_chargeorderdeteli datetime NOT NULL, --最后更新时间
FK_chargeorderId INT FOREIGN KEY REFERENCES chargeorder(chargeorderId) -- 创建外键约束chargeorderId
)
GO

　-- INSERT SQL

-- Product
INSERT INTO Product(productName,productCordNo,productPrice,last_updatetime) VALUES
('健齿白牙膏','20192200898E45',12.00,GETDATE()),
('健齿白牙膏-优惠款','20192200898E50',10.00,GETDATE()),
('黑人牙膏-A款','20192200898E46',24.00,GETDATE()),
('黑人牙膏-B款','20192200898E47',36.00,GETDATE()),
('高露洁牙膏-A款','20192200898E48',18.00,GETDATE()),
('高露洁牙膏-B款','20192200898E49',20.00,GETDATE())
GO
-- chargeorder
INSERT INTO chargeorder(chargeorderPrice,Number,paytime,last_updatetime_chargeorder,FK_ProductId) VALUES
(12.00,3,GETDATE(),GETDATE(),1),
(24.00,3,GETDATE(),GETDATE(),3),
(18.00,3,GETDATE(),GETDATE(),5)
GO
-- chargeorderdeteli
INSERT INTO chargeorderdeteli(chargeorderdeteliName,chargeorderdeteliPyType,chargeorderdeteliPySee,last_updatetime_chargeorderdeteli,FK_chargeorderId) VALUES
('健齿白牙膏',1,219,GETDATE(),1),
('黑人牙膏-A款',3,4990,GETDATE(),2),
('高露洁牙膏-A款',3,4936,GETDATE(),3)
GO

①触发器-insert

　　-- 触发器新增：对Product表插入的物品价格进行insert时候触发，价格不能为负数，否则触发器触发回滚，插入失败

-- 触发器新增：对Product表插入的物品价格进行insert时候触发，价格不能为负数，否则触发器触发回滚，插入失败
IF OBJECT_ID(N'tg_insert',N'TR') IS NOT NULL
    DROP TRIGGER tg_insert
GO
CREATE TRIGGER tg_insert
ON Product
FOR INSERT
AS
BEGIN
    DECLARE @Price decimal(10,2)
    SELECT @Price= Product.productPrice FROM Product INNER JOIN inserted ON Product.productPrice=inserted.productPrice
    PRINT @Price
    IF(@Price<0.00)
        BEGIN
            RAISERROR('插入的商品价格必须大于0.00',16,8)
            ROLLBACK TRAN
        END
END
GO

　　--执行测试SQL：价格为负数

INSERT INTO Product(productName,productCordNo,productPrice,last_updatetime) VALUES
('测试数据1','20192200898E45',-1,GETDATE())
GO

　　--测试结果：

②触发器-update

-- 触发器更新：更新chargeorder的chargeorderId的同时，更新chargeorderdeteli表FK_chargeorderId，这里属性设定为自增了，所以无法进行修改，其他update操作同理
IF OBJECT_ID(N'tg_update',N'TR') IS NOT NULL
    DROP TRIGGER tg_update
GO
CREATE TRIGGER tg_update
ON chargeorder
FOR UPDATE
AS
BEGIN
    IF UPDATE(chargeorderId)
    UPDATE chargeorderdeteli SET chargeorderdeteli.FK_chargeorderId=inserted.chargeorderId FROM chargeorder,inserted,deleted WHERE chargeorder.chargeorderId=deleted.chargeorderId
    IF @@ERROR>0
    BEGIN
        RAISERROR('操作失败',16,8)
        ROLLBACK TRAN
    END
END
GO

③触发器-delete

   -- 修改原始delete操作，改为update

-- 将原始delete操作修改为update操作
IF OBJECT_ID(N'tg_deleteupdate',N'TR') IS NOT NULL
    DROP TRIGGER tg_deleteupdate
GO
CREATE TRIGGER tg_deleteupdate
ON Product
INSTEAD OF DELETE
AS
BEGIN
    DECLARE @Product_Id int
    set @Product_Id=(SELECT Product.productId FROM Product INNER JOIN deleted ON Product.productId=deleted.productId)
    UPDATE Product SET productName='被修改的数据' WHERE Product.productId=@Product_Id
    IF @@ERROR>0
    BEGIN
        RAISERROR('操作失败',16,8)
        ROLLBACK TRAN
    END
END
GO

　-- 原数据形式

   -- 执行delete后结果

　-- 执行delete操作往Log_delete日志中添加被删除的记录

IF EXISTS(select * from sysobjects where name='Log_delete')
drop table Log_delete
go
create table Log_delete(
logContent varchar(255),
last_updatetime datetime
)
go

-- 执行删除操作delete触发触发器，向Log_delete表中添加被删除的记录信息
IF OBJECT_ID(N'tg_delete',N'TR') IS NOT NULL
    DROP TRIGGER tg_delete
GO
CREATE TRIGGER tg_delete
ON Product
FOR DELETE
AS
BEGIN
    DECLARE @Product_Id VARCHAR(255),@TIME DATETIME
    SELECT @Product_Id=productId FROM deleted SET @TIME=GETDATE()
    INSERT INTO Log_delete(logContent,last_updatetime) VALUES('{被删除的物品Id为：'+@Product_Id+'}',@TIME)
    IF @@ERROR>0
    BEGIN
        RAISERROR('！！！！',16,8)
        ROLLBACK TRAN
    END
END
GO

　-- 测试数据：测试将当前表的前面个tg_deleteupdate触发器禁用

 　-- 测试结果

4.问题

①多条insert语句一起执行，触发器失效？不做价格控制

 ②delete插入到log表的时候，删除一个不存在的ID插入空数据？

 

 ③使用IN来包含，只记录最前数据

 

5.对应第4点解决方案

①在insert或者delete的时候，多条并行数据插入(INSERT INTO Product(productName,productCordNo,productPrice,last_updatetime) VALUES('测试数据001','20192200898E49',20,GETDATE()),('测试数据002','20192200898E49',20.00,GETDATE())GO)或者delete(DELETE FROM Product WHERE productId IN(25,26)GO)的方式运行，触发触发器将这部分变动内容写入到log时候只成功第一条返回结果？

　-- 处理方法：使用游标(参考文章：http://www.baikeyang.com/code/97228.html)

-- 执行删除操作delete触发触发器，向Log_delete表中添加被删除的记录信息，支持含IN作为集合条件
IF OBJECT_ID(N'tg_delete',N'TR') IS NOT NULL
    DROP TRIGGER tg_delete
GO
CREATE TRIGGER tg_delete
ON Product
FOR DELETE 
AS
BEGIN
    DECLARE @Product_Id VARCHAR(255),@TIME DATETIME
    DECLARE CUR_DELETE CURSOR FOR SELECT deleted.productId FROM deleted  /*声明游标并指明游标操作的对象*/
    SELECT @Product_Id=productId FROM deleted
    SET @TIME=GETDATE()
    OPEN CUR_DELETE -- 打开游标
    FETCH NEXT FROM CUR_DELETE INTO @Product_Id --读取下一行
    WHILE @@FETCH_STATUS=0
    BEGIN
    PRINT '@@Start'
    INSERT INTO Log_delete(logContent,last_updatetime) VALUES('{被删除的物品Id为：'+@Product_Id+'}',@TIME)
    PRINT '@@NEXT PgDn'
    FETCH NEXT FROM CUR_DELETE INTO @Product_Id -- 读取下一行
        IF @@ERROR>0
        BEGIN
            RAISERROR('过程错误',16,8)
            ROLLBACK TRAN
        END
    END
    CLOSE CUR_DELETE -- 关闭游标
    DEALLOCATE CUR_DELETE
    PRINT '@@successfully'
END
GO

　-- 测试数据：

　-- 测试结果：

　-- 日志中添加其他字段信息

-- 执行删除操作delete触发触发器，向Log_delete表中添加被删除的记录信息，支持含IN作为集合条件
IF OBJECT_ID(N'tg_delete',N'TR') IS NOT NULL
    DROP TRIGGER tg_delete
GO
CREATE TRIGGER tg_delete
ON Product
FOR DELETE 
AS
BEGIN
    DECLARE @Product_Id VARCHAR(255),@TIME DATETIME,@ProductName VARCHAR(50)
    DECLARE CUR_DELETE CURSOR FOR SELECT productId,productName FROM deleted  /*声明游标并指明游标操作的对象*/
    --SELECT @Product_Id=productId,@ProductName=productName FROM deleted
    SET @TIME=GETDATE()
    OPEN CUR_DELETE -- 打开游标
    FETCH NEXT FROM CUR_DELETE INTO @Product_Id,@ProductName --读取下一行
    WHILE @@FETCH_STATUS=0
    BEGIN
    PRINT '@@Start'
    INSERT INTO Log_delete(logContent,last_updatetime) VALUES('{被删除的物品Id为：'+@Product_Id+'，'+@ProductName+'}',@TIME)
    PRINT '@@NEXT PgDn'
    FETCH NEXT FROM CUR_DELETE INTO @Product_Id,@ProductName -- 读取下一行
        IF @@ERROR>0
        BEGIN
            RAISERROR('过程错误',16,8)
            ROLLBACK TRAN
        END
    END
    CLOSE CUR_DELETE -- 关闭游标
    DEALLOCATE CUR_DELETE
    PRINT '@@successfully'
END
GO