SQLServer 事务与触发器

触发器（Triggers）和存储过程（Stored Procedures）是 SQL Server 中的两个不同的数据库对象，它们具有不同的用途和特性。

触发器（Triggers）：
- 触发时机： 触发器是在表上执行特定操作时自动触发的。可以在插入、更新或删除数据时触发。触发器分为 AFTER 和 INSTEAD OF 两种类型，分别表示在触发动作之后执行和代替触发动作执行。
- 事件关联性： 触发器与特定表的特定事件关联，当事件发生时，触发器会自动执行。
- 用途： 触发器通常用于实施数据完整性、强制业务规则和自动记录更改历史等方面。
存储过程（Stored Procedures）：
- 调用时机： 存储过程是一组 SQL 语句的预编译集合，存储在数据库中以供反复使用。存储过程不像触发器那样自动触发，而是需要显式调用。
- 用途： 存储过程通常用于封装和重用一系列 SQL 语句，提高代码的可维护性和复用性。它们可以接受参数、执行复杂的业务逻辑、返回结果等。

关键区别包括：

触发器是由数据库管理系统自动触发的，与特定表的特定事件关联；而存储过程需要显式调用。
触发器通常用于实现数据完整性、业务规则等，而存储过程用于封装和重用 SQL 代码，提高代码的可维护性和复用性。

当数据表1插入一条记录时，通过触发器添加数据表2内容

触发器写法：

CREATE TRIGGER tr_AppReservedProject_Insert ON dbo.app_reservedProject AFTER INSERT AS BEGIN
    
    SET NOCOUNT ON;
-- 插入相关的 reservedTime
    INSERT INTO dbo.app_reservedTime ( reservationProjectID, projectType, TimeSlot, Quota, sGraceTime, sLimitedTime, nSort, nIsEnable, dtUpdate ) SELECT
    i.reservationProjectID,
    i.projectType,
    CASE
            
            WHEN i.projectType = 1 THEN
            CONVERT ( nvarchar ( 5 ), DATEADD( HOUR, 8 + n, '1900-01-01' ), 108 ) + '-' + CONVERT ( nvarchar ( 5 ), DATEADD( HOUR, 9 + n, '1900-01-01' ), 108 ) 
            WHEN i.projectType = 2 THEN
            CONVERT ( nvarchar ( 5 ), DATEADD( MINUTE, 30 * n, DATEADD( HOUR, 8, '1900-01-01' ) ), 108 ) + ' - ' + CONVERT (
                nvarchar ( 5 ),
                DATEADD( MINUTE, 30 * ( n + 1 ), DATEADD( HOUR, 8, '1900-01-01' ) ),
                108 
            ) 
        END AS TimeSlot,
        0 AS Quota,
        0 AS sGraceTime,
        0 AS sLimitedTime,
        n AS nSort,
        1 AS nIsEnable,
        GETDATE( ) AS dtUpdate 
    FROM
        inserted i
        CROSS JOIN (
        SELECT TOP
            (
            CASE
                    
                    WHEN ( SELECT TOP 1 projectType FROM inserted ) = 1 THEN
                    7 
                    WHEN ( SELECT TOP 1 projectType FROM inserted ) = 2 THEN
                    14 ELSE 0 
                END 
                ) ROW_NUMBER ( ) OVER ( ORDER BY ( SELECT NULL ) ) - 1 AS n 
            FROM
                master.dbo.spt_values 
            WHERE
                type = 'P' 
            ) AS numbers 
        WHERE
        i.projectType IN ( 1, 2 );
END;

使用

INSERT INTO app_reservedProject 
                      (projectID, projectName, projectType, nIsEnable, disableStartTime, disableEndTime, dtCreate, dtUpdate)
                      VALUES 
                      (1, 1, 1, 1, GETDATE(), GETDATE(), GETDATE(), GETDATE())

查看数据库中的触发器：

SELECT 
    name AS TriggerName,
    OBJECT_NAME(parent_id) AS TableName,
    create_date AS CreatedDate,
    modify_date AS LastModifiedDate
FROM 
    sys.triggers;

 

-- 删除名为 tr_AppReservedProject_Insert 的触发器
DROP TRIGGER tr_AppReservedProject_Insert;

创建存储过程：

ALTER PROCEDURE proc_InsertReservedTime
    @reservationProjectID bigint,
    @projectType int,
    @Quota int = 1000,
    @sGraceTime int = 10,
    @sLimitedTime int = -20,
    @nSort int = 1,
    @nIsEnable bit = 1
AS 
BEGIN
    SET NOCOUNT ON;

    -- 插入相关的 reservedTime
    INSERT INTO dbo.app_reservedTime 
        (reservationProjectID, projectType, TimeSlot, Quota, sGraceTime, sLimitedTime, nSort, nIsEnable, dtUpdate)
    SELECT
        @reservationProjectID,
        @projectType,
        CASE 
            WHEN @projectType = 1 THEN
                CONVERT(nvarchar(5), DATEADD(HOUR, 8 + n, '1900-01-01'), 108) + '-' + CONVERT(nvarchar(5), DATEADD(HOUR, 9 + n, '1900-01-01'), 108)
            WHEN @projectType = 2 THEN
                CONVERT(nvarchar(5), DATEADD(MINUTE, 30 * n, DATEADD(HOUR, 8, '1900-01-01')), 108) + ' - ' + CONVERT(nvarchar(5), DATEADD(MINUTE, 30 * (n + 1), DATEADD(HOUR, 8, '1900-01-01')), 108)
        END AS TimeSlot,
        @Quota AS Quota,
        @sGraceTime AS sGraceTime,
        @sLimitedTime AS sLimitedTime,
        n AS nSort,
        @nIsEnable AS nIsEnable,
        GETDATE() AS dtUpdate
    FROM
        (
            SELECT TOP (CASE WHEN @projectType = 1 THEN 7 WHEN @projectType = 2 THEN 14 ELSE 0 END) 
                ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS n 
            FROM master.dbo.spt_values 
            WHERE type = 'P'
        ) AS numbers
    WHERE
        @projectType IN (1, 2);
END;

拆分为存储过程1：

ALTER PROCEDURE proc_InsertReservedTime1
    @reservationProjectID bigint
AS 
BEGIN
    SET NOCOUNT ON;

    -- 插入相关的 reservedTime
    INSERT INTO dbo.app_reservedTime 
        (reservationProjectID, projectType, TimeSlot, Quota, sGraceTime, sLimitedTime, nSort, nIsEnable, dtUpdate)
    SELECT
        @reservationProjectID,
        1 AS projectType,
        CONVERT(nvarchar(5), DATEADD(HOUR, 8 + n, '1900-01-01'), 108) + '-' + CONVERT(nvarchar(5), DATEADD(HOUR, 9 + n, '1900-01-01'), 108) AS TimeSlot,
        1000 AS Quota,
        10 AS sGraceTime,
        -20 AS sLimitedTime,
        n AS nSort,
        1 AS nIsEnable,
        GETDATE() AS dtUpdate
    FROM
        (
            SELECT TOP 7 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS n 
            FROM master.dbo.spt_values 
            WHERE type = 'P'
        ) AS numbers;
END;

拆分为存储过程2：

ALTER PROCEDURE proc_InsertReservedTime2 
    @reservationProjectID bigint
AS 
BEGIN
    SET NOCOUNT ON;

    -- 插入相关的 reservedTime
    INSERT INTO dbo.app_reservedTime 
        (reservationProjectID, projectType, TimeSlot, Quota, sGraceTime, sLimitedTime, nSort, nIsEnable, dtUpdate)
    SELECT
        @reservationProjectID,
        2 AS projectType,
                CONVERT(nvarchar(5), DATEADD(MINUTE, 30 * n, DATEADD(HOUR, 8, '1900-01-01')), 108) + ' - ' + CONVERT(nvarchar(5), DATEADD(MINUTE, 30 * (n + 1), DATEADD(HOUR, 8, '1900-01-01')), 108) AS TimeSlot,

        1000 AS Quota,
        10 AS sGraceTime,
        -20 AS sLimitedTime,
        n AS nSort,
        1 AS nIsEnable,
        GETDATE() AS dtUpdate
    FROM
        (
            SELECT TOP 14 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS n 
            FROM master.dbo.spt_values 
            WHERE type = 'P'
        ) AS numbers;
END;

下面为一些SQLServer相关内容。

1.所操作的表结构：

CREATE TABLE [dbo].[app_reservedProject] (
  [reservationProjectID] bigint  IDENTITY(1,1) NOT NULL,
  [projectID] char(36) COLLATE Chinese_PRC_CI_AS  NOT NULL,
  [projectName] nvarchar(255) COLLATE Chinese_PRC_CI_AS  NOT NULL,
  [projectType] int  NOT NULL,
  [nIsEnable] bit  NOT NULL,
  [disableStartTime] datetime  NULL,
  [disableEndTime] datetime  NULL,
  [dtCreate] datetime  NOT NULL,
  [dtUpdate] datetime  NOT NULL
)

CREATE TABLE [dbo].[app_reservedTime] (
  [reservedTimeID] bigint  IDENTITY(1,1) NOT NULL,
  [reservationProjectID] bigint  NOT NULL,
  [projectType] int  NOT NULL,
  [TimeSlot] nvarchar(100) COLLATE Chinese_PRC_CI_AS  NOT NULL,
  [Quota] int  NOT NULL,
  [sGraceTime] int  NOT NULL,
  [sLimitedTime] int  NOT NULL,
  [nSort] int  NOT NULL,
  [nIsEnable] bit  NOT NULL,
  [dtUpdate] datetime  NOT NULL
)

2.测试存储过程可使用以下方式：

DECLARE @TableName varchar(5000) = 'xx'
DECLARE @PrimaryKey varchar(50) = 'xx'
DECLARE @FilterFields varchar(1000) = 'xx'

DECLARE @WhereStr varchar(1000) = '1=1'
DECLARE @PageSize int = 10
DECLARE @PageIndex int = 1
DECLARE @OrderField varchar(1000) = 'xx'
DECLARE @OrderSign int = 0
DECLARE @TotalCount int
PRINT 'TableName: ' + @TableName
PRINT 'PrimaryKey: ' + @PrimaryKey
PRINT 'WhereStr: ' + @WhereStr
PRINT 'PageSize: ' + CAST(@PageSize AS VARCHAR(10))
PRINT 'PageIndex: ' + CAST(@PageIndex AS VARCHAR(10))
PRINT 'OrderField: ' + @OrderField
PRINT 'OrderSign: ' + CAST(@OrderSign AS VARCHAR(10))
PRINT 'FilterFields: ' + @FilterFields
EXEC [dbo].[xx]
    @TableName = @TableName,
    @PrimaryKey = @PrimaryKey,
    @WhereStr = @WhereStr,
    @PageSize = @PageSize,
    @PageIndex = @PageIndex,
    @OrderField = @OrderField,
    @OrderSign = @OrderSign,
    @FilterFields = @FilterFields,
    @TotalCount = @TotalCount OUTPUT