SQLserver中的触发器和存储过程

在 SQL Server 中，触发器是一种特殊的存储过程，它在指定的数据库表上发生特定的数据修改事件时自动执行。触发器可以用于执行各种任务，如数据验证、数据审计、自动更新相关表等。

触发器的类型

SQL Server 支持以下几种类型的触发器：

1. INSERT 触发器：在向表中插入新行时触发。

2. UPDATE 触发器：在修改表中的现有行时触发。

3. DELETE 触发器：在从表中删除行时触发。

4. INSTEAD OF 触发器：在执行 UPDATE、DELETE 或 INSERT 操作之前触发，允许你自定义操作而不是执行默认操作。

触发器的创建

创建触发器的基本语法如下：

CREATE TRIGGER trigger_name
ON table_name
AFTER|INSTEAD OF {INSERT | UPDATE | DELETE}
AS
BEGIN
    -- 触发器的 SQL 代码
END

示例

假设我们有一个名为 Employees 的表，我们想要在每次插入新员工记录时自动记录这一操作到 AuditLog 表中。

1. 创建触发器：

CREATE TRIGGER trg_AfterInsertEmployee
ON Employees
AFTER INSERT
AS
BEGIN
    INSERT INTO AuditLog (Action, EmployeeID, ChangedDate)
    SELECT 'Insert', EmployeeID, GETDATE()
    FROM inserted
END

在这个例子中，trg_AfterInsertEmployee 是触发器的名称，它在 Employees 表上定义。当有新记录插入 Employees 表时，触发器会将操作记录到 AuditLog 表中。

1. 使用 INSTEAD OF 触发器：

如果你想要自定义 INSERT 操作，可以使用 INSTEAD OF 触发器。例如，假设我们想要在插入新员工之前验证数据：

CREATE TRIGGER trg_InsteadOfInsertEmployee
ON Employees
INSTEAD OF INSERT
AS
BEGIN
    IF (SELECT COUNT(*) FROM inserted WHERE Age < 18) > 0
    BEGIN
        RAISERROR ('Cannot insert employee under 18 years old.', 16, 1)
        ROLLBACK TRANSACTION
        RETURN
    END
    ELSE
    BEGIN
        INSERT INTO Employees (EmployeeID, Name, Age)
        SELECT EmployeeID, Name, Age
        FROM inserted
    END
END

在这个例子中，如果尝试插入年龄小于 18 岁的员工，触发器将阻止插入并返回错误。

注意事项

• 触发器可以影响数据库性能，特别是在高频率更新的表上。

• 触发器中的错误可能导致数据不一致，因此在生产环境中使用触发器时需要谨慎。

• 触发器可以嵌套，但嵌套触发器可能导致复杂的逻辑和性能问题，应尽量避免。

创建一个insert的触发器

CREATE TRIGGER TRIGGER_STUDENT_AFTERINSERT ON STUDENT 
    AFTER INSERT
    AS 
    DECLARE @STUID INT, @STUNAME NVARCHAR(50),@STUAGE INT
    SELECT @STUID=STUID, @STUNAME=STUNAME,@STUAGE=STUAGE FROM STUDENT
    PRINT CONVERT(VARCHAR,@STUID)+','+@STUNAME+','+ CONVERT(VARCHAR,@STUAGE)
    INSERT INTO STUDENT01(STUID,STUNAME,STUAGE) VALUES(@STUID,@STUNAME,@STUAGE)

SQLserver中存储过程

在 SQL Server 中，存储过程是一组为了完成特定功能的 SQL 语句集合，这些语句被保存在数据库中，可以通过一个调用语句来执行。存储过程可以接收参数、返回结果，并且可以进行复杂的逻辑处理。

存储过程的优点

1. 性能优化：预编译的 SQL 语句可以提高执行效率。

2. 安全性：通过存储过程，可以限制用户直接访问数据库表，只允许通过存储过程来操作数据，从而提高数据安全性。

3. 重用性：可以重复使用存储过程，减少代码冗余。

4. 减少网络流量：逻辑封装在服务器端，减少了客户端和服务器端之间的通信。

5. 事务管理：可以在存储过程中方便地管理事务。

创建存储过程

创建存储过程的基本语法如下：

CREATE PROCEDURE ProcedureName
    @param1 DataType,
    @param2 DataType
AS
BEGIN
    -- SQL statements
END

示例

假设我们想要创建一个存储过程来插入新员工的信息：

CREATE PROCEDURE AddEmployee
    @EmployeeID INT,
    @EmployeeName NVARCHAR(100),
    @Department NVARCHAR(100)
AS
BEGIN
    INSERT INTO Employees (EmployeeID, Name, Department)
    VALUES (@EmployeeID, @EmployeeName, @Department)
END

调用存储过程

调用存储过程使用 EXEC 或 EXECUTE 语句：

EXEC AddEmployee @EmployeeID = 1, @EmployeeName = 'John Doe', @Department = 'Sales'

参数化存储过程

存储过程可以有输入参数、输出参数和返回值：

CREATE PROCEDURE GetEmployeeByID
    @EmployeeID INT,
    @EmployeeName NVARCHAR(100) OUTPUT
AS
BEGIN
    SELECT @EmployeeName = Name FROM Employees WHERE EmployeeID = @EmployeeID
END

调用带有输出参数的存储过程：

DECLARE @Name NVARCHAR(100)
EXEC GetEmployeeByID @EmployeeID = 1, @EmployeeName = @Name OUTPUT
SELECT @Name AS EmployeeName

错误处理

存储过程中可以使用 TRY...CATCH 语句进行错误处理：

CREATE PROCEDURE ProcessData
AS
BEGIN
    TRY
        -- 可能出错的 SQL 语句
   END
   CATCH
   BEGIN
       SELECT 
           ERROR_NUMBER() AS ErrorNumber,
           ERROR_MESSAGE() AS ErrorMessage
   END
END

存储过程的修改和删除

• 修改存储过程：使用 ALTER PROCEDURE 语句。

• 删除存储过程：使用 DROP PROCEDURE 语句。

ALTER PROCEDURE ProcedureName
AS
BEGIN
    -- 新的 SQL 语句
END
​
DROP PROCEDURE ProcedureName

注意事项

• 存储过程可以非常复杂，包含循环、条件语句等。

• 存储过程可以调用其他存储过程。

• 存储过程的权限可以精细控制，例如，可以限制谁可以执行特定的存储过程。

• 存储过程的执行可以通过动态 SQL 来实现更高级的功能。

使用存储过程按日期生产编号

CREATE PROC PROCCREATENO  AS 
BEGIN 
DECLARE @NO VARCHAR(20),@LASTSTOCKNO VARCHAR(20) ,@STRTIMESTR VARCHAR(8)
SET @STRTIMESTR= CONVERT(VARCHAR(8),GETDATE(),112)
SELECT @LASTSTOCKNO =(SELECT TOP 1 STOCKNO FROM STOCK WHERE SUBSTRING(STOCKNO,1,8)=@STRTIMESTR ORDER BY STOCKNO DESC)
PRINT @LASTSTOCKNO
IF @LASTSTOCKNO IS NULL
BEGIN 
SET @NO= @STRTIMESTR+'00001'
END
ELSE 
BEGIN
DECLARE @LEN INT ,@LASTNO INT,@TEMPNO VARCHAR(5),@N INT
SET @LASTNO =CONVERT(INT,SUBSTRING(@LASTSTOCKNO,9,5))+1
SET @LEN=LEN(@LASTNO)
SET @TEMPNO=CONVERT(VARCHAR,@LASTNO)
SET @N=0;
WHILE(@N<5-@LEN)
BEGIN
SET @TEMPNO='0'+@TEMPNO
SET @N+=1
END
SET @NO=@STRTIMESTR+@TEMPNO
END
INSERT INTO STOCK(STOCKNO) VALUES(@NO)
END

使用EXEC PROCCREATENO执行结果

结果为