triggerdemo25.sql

来自「T-SQL示例大全」· SQL 代码 · 共 70 行

/*
文件名称: TriggerDemo25.sql
*/
USE 北风贸易
GO

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

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

--建立一个数据表
CREATE TABLE SampleTable
(
Id char(10),
FirstName nvarchar(20),
LastName  nvarchar(30)
)
GO

-- 建立一个视图，且其中一个字段是由原始数据表的两个字段组合而成
CREATE VIEW MyView
AS
SELECT Id,FirstName +LastName AS CombinedName FROM SampleTable

GO

-- 为视图 MyView 建立一个 INSTEAD OF INSERT 触发程序
CREATE TRIGGER InsteadInsertTrigger
ON MyView
INSTEAD OF INSERT
AS
BEGIN

   INSERT SampleTable
      SELECT Id,
         -- 截取出姓氏的字符串
         SUBSTRING(
            CombinedName,
            1,
            (CHARINDEX(';', CombinedName) - 1)
            ),
         -- 截取出名字的字符串
         SUBSTRING(
            CombinedName,
            (CHARINDEX(';', CombinedName) + 1),
            DATALENGTH(CombinedName)
            )
      FROM inserted
END

GO

--新增数据至视图
INSERT MyView (Id,CombinedName) VALUES ('A123456781','章;立民')
INSERT MyView (Id,CombinedName) VALUES ('B123456781','庄;文升')
INSERT MyView (Id,CombinedName) VALUES ('C123456781','章;舒涵')
GO

--查阅数据表的数据内容
SELECT * FROM SampleTable
GO

--查阅视图的数据内容
SELECT * FROM MyView
GO