视图触发器

 

触发器综述之五

 

视图触发器

   

在SQL SERVER 2000增加了视图触发器功能后，利用它可以解决以前版本对视图数据修改引起的基表数据修改不正确和有时会报错的问题。视图触发器都是用INSTEAD OF 触发器，根据业务需要，和视图基表的情况，把对视图数据的修改解释为对基表数据的修改，从而实现客户端只需要直接对视图操作，而不必了解基表的情况，把视图当作一个表来对待。

从技术上，这类触发器很可能把INSERT，UPDATE，DELETE分来写触发器，从而降低触发器的难度，但是都要求对视图的业务关系非常熟悉，知道视图数据的各种修改对应基表做什么样的变化。从性能上来说，视图的增删改操作比较频繁，数据量也不小，对性能的要求比较高，需要在触发器代码注意性能的提高，必要的时候需要用基表的索引或者视图的索引来提高性能。

 

下面用一个简化了的主从表视图例子来说明这类触发器。需求如下：

         一个主表TBM和一个从表TBD是一对多关系，形成一个视图，要求客户端可以直接增删改视图的数据：

 

--建立主表

CREATE TABLE TBM (

ID INT NOT NULL PRIMARY KEY,               --ID

MDATA INT NOT NULL                       --数据

)

GO

 

--建立从表

CREATE TABLE TBD (

ID INT NOT NULL,             --ID

ITM INT NOT NULL,                   --序号

DDATA INT NOT NULL,              --数据

CONSTRAINT PK_TBD PRIMARY KEY CLUSTERED (ID,ITM)

)

GO

 

--建立视图

CREATE VIEW V_DETAIL

AS

SELECT A.ID,A.MDATA,B.ITM,B.DDATA

FROM TBM A LEFT JOIN TBD B

ON A.ID=B.ID

 

GO

 

--插入几条原始数据

INSERT TBM SELECT 1,101 UNION ALL SELECT 2,102

 

INSERT TBD SELECT 1,1,211 UNION ALL SELECT 1,2,212

 

GO

 

--视图显示

SELECT * FROM V_DETAIL

GO

 

--结果数据如下

 

 

 

 

 

--建立视图触发器，有了视图触发器后，客户端可以只对视图进行增删改操作

CREATE TRIGGER TR_V_DETAIL

ON V_DETAIL

INSTEAD OF INSERT,UPDATE,DELETE

AS

 

SET NOCOUNT ON

--删除从表

DELETE A

FROM TBD A,DELETED D

WHERE A.ID=D.ID

AND A.ITM=D.ITM

AND NOT EXISTS (             -- 修改数据的情况不需要删除

         SELECT 1

         FROM INSERTED

         WHERE ID=D.ID

         AND ITM=D.ITM

)

 

--删除主表

DELETE A

FROM TBM A,DELETED D

WHERE A.ID=D.ID

AND NOT EXISTS (             -- 修改数据的情况不需要删除

         SELECT 1

         FROM INSERTED

         WHERE ID=D.ID

)

AND NOT EXISTS (             -- 有其他从表数据不能删除

         SELECT     1

         FROM TBD

         WHERE ID=D.ID

)

 

--修改从表数据

UPDATE A SET

         DDATA=I.DDATA

FROM TBD A,INSERTED I

WHERE A.ID=I.ID

AND A.ITM=I.ITM

 

--修改主表数据

UPDATE A SET

         MDATA=I.MDATA

FROM TBM A,INSERTED I

WHERE A.ID=I.ID

 

--插入主表数据

INSERT TBM(ID,MDATA)

SELECT ID,MAX(MDATA) AS MDATA

FROM INSERTED I

WHERE NOT EXISTS (

         SELECT 1

         FROM TBM

         WHERE ID=I.ID

)

GROUP BY ID

 

--插入从表数据

INSERT TBD(ID,ITM,DDATA)

SELECT ID,ITM,DDATA

FROM INSERTED I

WHERE NOT EXISTS (

         SELECT 1

         FROM TBD

         WHERE ID=I.ID

         AND ITM=I.ITM

)

 

GO

 

 

--测试一条插入原有主表的数据

INSERT V_DETAIL(ID,ITM,MDATA,DDATA) VALUES(2,1,102,221)

 

SELECT * FROM V_DETAIL

SELECT * FROM TBM

SELECT * FROM TBD

GO

 

--结果只插入从表

 

-

 

-测试一次插入两条的数据

INSERT V_DETAIL(ID,ITM,MDATA,DDATA) SELECT 3,1,103,231 UNION ALL SELECT 3,2,103,232

 

SELECT * FROM V_DETAIL

SELECT * FROM TBM

SELECT * FROM TBD

GO

 

--结果只插入一条主表数据

 

-

 

-测试修改一条从表数据

UPDATE V_DETAIL SET

         DDATA=1000

WHERE ID=3

AND ITM=1

 

SELECT * FROM V_DETAIL

SELECT * FROM TBM

SELECT * FROM TBD

GO

 

--结果

 

 

--测试修改主从两表数据

UPDATE V_DETAIL SET

         DDATA=900,

        MDATA=800

WHERE ID=3

 

SELECT * FROM V_DETAIL

SELECT * FROM TBM

SELECT * FROM TBD

GO

 

--结果

 

 

 

--测试删除只有一条从表数据的纪录

DELETE V_DETAIL

WHERE ID=2

AND ITM=1

 

SELECT * FROM V_DETAIL

SELECT * FROM TBM

SELECT * FROM TBD

GO

 

--结果主表数据一起删除

 

-

 

-测试删除有多条从表数据的记录中的一条

DELETE V_DETAIL

WHERE ID=1

AND ITM=1

 

SELECT * FROM V_DETAIL

SELECT * FROM TBM

SELECT * FROM TBD

GO

 

--结果主表数据保留

 

-

 

-测试删除一个主表ID的所有记录

 

DELETE V_DETAIL

WHERE ID=3

 

SELECT * FROM V_DETAIL

SELECT * FROM TBM

SELECT * FROM TBD

GO

 

--结果主从表数据一起删除

 

 

 

 

总结：利用视图触发器可以使得视图具有直接增删改的功能，这样在某些应用中可以使得前端程序编写简单，把视图当作一个表来操作。