triggerdemo24.sql

T-SQL示例大全

/*
文件名称: TriggerDemo24.sql
目        的: 示范如何利用 INSTEAD OF DELETE 触发程序去删除视图的来源数据表的数据记录
*/
USE 北风贸易
GO

IF exists (select * from dbo.sysobjects where id = object_id(N'dbo.BaseTable') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    DROP TABLE dbo.BaseTable
GO

IF exists (select * from dbo.sysobjects where id = object_id(N'dbo.InsteadView') and OBJECTPROPERTY(id, N'IsView') = 1)
    DROP VIEW dbo.InsteadView
GO

-- 建立一个数据表
CREATE TABLE BaseTable
(
编号 int IDENTITY(1,1) PRIMARY KEY CLUSTERED,
颜色 nvarchar(10) NOT NULL,
材质 nvarchar(10) NOT NULL,
颜色材质 AS (颜色 + 材质)
  )
GO

INSERT BaseTable (颜色, 材质) VALUES (N'蓝色', N'塑胶')
INSERT BaseTable (颜色, 材质) VALUES (N'红色', N'木头')
INSERT BaseTable (颜色, 材质) VALUES (N'白色', N'棉花')
SELECT * FROM BaseTable
GO

--建立一个包含原始数据表所有字段的视图
CREATE VIEW InsteadView
AS
SELECT 编号, 颜色, 材质, 颜色材质 FROM BaseTable
GO

-- 在视图上建立一个 INSTEAD OF DELETE 触发程序
CREATE TRIGGER InsteadDeleteTrigger
ON InsteadView
INSTEAD OF DELETE
AS
BEGIN
  -- 建立一道 DELETE 命令
  DELETE BaseTable
       FROM deleted
       WHERE BaseTable.编号 = deleted.编号
END
GO

-- 删除视图的数据记录
DELETE InsteadView WHERE 编号 = 2
GO

-- 查看执行 DELETE 命令后，视图的数据记录有何变化
SELECT 编号, 颜色, 材质, 颜色材质
    FROM InsteadView

GO

-- 查看执行 DELETE 命令后，数据表的数据记录有何变化
SELECT 编号, 颜色, 材质, 颜色材质
    FROM BaseTable

GO