常用的sql server 触发器 和 事务

 

触发器就是在对表进行：INSERT， UPDATE， DELETE时要执行的sequal语句，从而来控制这些操作或这些操作要引起的链锁反应；

 

触发器分为3种类型:INSERT,UPDATE,DELETE;

 

假设学生信息管理系统里有个需求是：不能在学生表里插入重覆的学号.下面演示用触发器来作控制：

 

--  delete existing trigger which name is tr_bas_student_i
IF EXISTS (SELECT 1 FROM SYSOBJECTS A WHERE A.ID = OBJECT_ID('tr_bas_student_i') AND A.TYPE = 'TR')
BEGIN
    DROP TRIGGER tr_bas_student_i
END
GO

--  create trigger of tr_bas_student_i for bas_student table
CREATE TRIGGER tr_bas_student_i
ON bas_student FOR INSERT
AS
BEGIN
    IF EXISTS (SELECT 1 FROM bas_student A WHERE A.student_no in (SELECT B.student_no FROM INSERTED B))
    BEGIN
        DECLARE @ERR_NO INT=5001, @ERR_MSG NVARCHAR(250)
        @@RAISERROR @ERR_NO @ERR_MSG END
END
GO

 

 

前面的IF判断语句是用来确保数据库不存在名为tr_bas_student_i的trigger，以便下面的创建能顺利进行；

 

CREATE 语句是为学生表：bas_student创建一个INESRT的触发器，名为：tr_bas_student_i，其中里面IF语句用到的 INSERTED是个要插入到表bas_student里的记录，判断她如果已存在一样的student_no就触发错误，并反回错误信 息：＠ERR_MSG,但这样做，虽然返回了触发了错误和返回错误信息，但表INSERTED里的记录还是插入到表里了，原因是引发了错误事件，但还是无 法阻止该插入动作的执行，不过还有个解决方案是在触发器里开个事务，当出错就执行事务回滚，下面是演示代码：

 

--  create trigger of tr_bas_student_i for bas_student table
CREATE TRIGGER tr_bas_student_i
ON bas_student FOR INSERT
AS
BEGIN
    --  establish an transaction
    BEGIN TRANSACTION
        IF EXISTS (SELECT 1 FROM bas_student A WHERE A.student_no in (SELECT B.student_no FROM INSERTED B))
        BEGIN
             DECLARE @ERR_NO INT=5001, @ERR_MSG NVARCHAR(250)
             @@RAISERROR @ERR_NO @ERR_MSG 
             ROLLBACK TRANSACTION
             RETURN
        END
        ELSE
        BEGIN
            COMMIT TRANSACTION
        END
END
GO