创建和触发sql2005视图中的触发器

     以前只知道表可以创建触发器，今天刚知道原来视图也可以。今天刚测试了一下 ，把遇到的一些小问题记录下来，省得以后再走弯路。

有表A和表B, 组成视图v_c ，在视图中直接添加触发器时，语法如下：

create TRIGGER TR_V_DETAIL

INSTEAD OF insert/update/delete
AS ...

注意点：

  1.是 “INSTEAD OF insert”，而不能直接使用 insert/update/delete,否则会报视图不存在或者语法错误。

  2.视图中的触发器，只是在视图修改的时候才会触发，（添加触发器后，可以用sql语句添加，修改视图的信息）
  
  3.给视图添加的触发器，在修改视图中的单张表或者视图中的多张表的时候，都不会触发该触发器，只是在添加，修改视图中的数据的时候，才会触发

今天把代码补上：

--建立主表

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

)

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
 

--插入主表数据           （这个不是太明白，为什么需要按照inserted表的id分组，取mdata的最大值）

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

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

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

 
SELECT * FROM V_DETAIL
SELECT * FROM TBM
SELECT * FROM TBD
GO
 --结果只插入从表

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

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
--结果只插入一条主表数据，两条从表数据

--3.测试修改一条从表数据

UPDATE V_DETAIL SET DDATA=1000 WHERE ID=3 AND ITM=1

SELECT * FROM V_DETAIL
SELECT * FROM TBM
SELECT * FROM TBD
GO
--结果：主表数据不变，从表数据更新

--4.测试修改主从两表数据
UPDATE V_DETAIL SET DDATA=900, MDATA=800 WHERE ID=3 
SELECT * FROM V_DETAIL
SELECT * FROM TBM
SELECT * FROM TBD
GO
--结果：主表数据修改，从表数据也修改

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

DELETE V_DETAIL WHERE ID=2 AND ITM=1
SELECT * FROM V_DETAIL
SELECT * FROM TBM
SELECT * FROM TBD
GO
--结果主表数据一起删除，执行的时候是先删除从表的数据，在删除主表的数据的时候，从表数据已经被删除，所以主表中的数据会被一起删除

--6.测试删除有多条从表数据的记录中的一条
DELETE V_DETAIL WHERE ID=1 AND ITM=1

SELECT * FROM V_DETAIL
SELECT * FROM TBM
SELECT * FROM TBD
GO
--结果主表数据保留，删除主表的时候，从表中还有数据未全部删除

--7.测试删除一个主表ID的所有记录
DELETE V_DETAIL WHERE ID=3

SELECT * FROM V_DETAIL
SELECT * FROM TBM
SELECT * FROM TBD
GO
--结果主从表数据一起删除 ？？