insert data in related tables through a view sql server

Hope someone can help with this. I've got two tables (structure below):

希望有人能帮忙解决这个问题。我有两张桌子(下面的结构):

Table1
DataYear INT Not Null,
ProvId INT Not Null,
LKId INT Not Null,
UpdateTime Datetime2(2) Not Null,
JTimes INT Not Null

Table2
DataYear INT Not Null,
ProvId INT Not Null,
LKId INT Not Null,
UpdateTime Datetime2(2) Not Null,
IsSuspect BIT,
Recs VARCHAR(10)

The PK's for both tables are the first 4 columns. The view is going to take all the values from Table 1 and the last two columns from table 2.

两个表的PK都是前4列。该视图将采用表1中的所有值和表2中的最后两列。

I want to do an insert data into the view and all columns in both tabels populated. Is this possible? How do I do it?

我想在视图中插入数据,并填充两个表格中的所有列。这可能吗?我该怎么做?

Hope someone can help

希望有人能提供帮助

Thanks

Alex

1 个解决方案

#1

You can use an INSEAD OF INSERT trigger on the view to insert data into multiple tables.

您可以在视图上使用INSEAD OF INSERT触发器将数据插入到多个表中。

CREATE TABLE dbo.Table1
    (
      DataYear int NOT NULL
    , ProvId int NOT NULL
    , LKId int NOT NULL
    , UpdateTime datetime2(2) NOT NULL
    , JTimes int NOT NULL
    , CONSTRAINT PK_Table1 PRIMARY KEY(
          DataYear
        , ProvId
        , LKId
        , UpdateTime)
    );

CREATE TABLE dbo.Table2
    (
      DataYear int NOT NULL
    , ProvId int NOT NULL
    , LKId int NOT NULL
    , UpdateTime datetime2(2) NOT NULL
    , IsSuspect bit
    , Recs varchar(10)
    , CONSTRAINT PK_Table2 PRIMARY KEY(
          DataYear
        , ProvId
        , LKId
        , UpdateTime)
    );
GO

CREATE VIEW dbo.View1 AS
SELECT 
      Table1.DataYear
    , Table1.ProvId
    , Table1.LKId
    , Table1.UpdateTime
    , Table1.JTimes
    , Table2.IsSuspect
    , Table2.Recs
FROM dbo.Table1
JOIN dbo.Table2 ON
      Table2.DataYear = Table1.DataYear
    AND Table2.ProvId = Table1.ProvId
    AND Table2.LKId = Table1.LKId
    AND Table2.UpdateTime = Table1.UpdateTime
GO

CREATE TRIGGER INSTEAD_OF_INSERT_View1 
ON dbo.View1 INSTEAD OF INSERT AS
INSERT INTO dbo.Table1(
      DataYear
    , ProvId
    , LKId
    , UpdateTime
    , JTimes)
SELECT 
      DataYear
    , ProvId
    , LKId
    , UpdateTime
    , JTimes
FROM inserted;

INSERT INTO dbo.Table2(
      DataYear
    , ProvId
    , LKId
    , UpdateTime
    , IsSuspect
    , Recs)
SELECT 
      DataYear
    , ProvId
    , LKId
    , UpdateTime
    , IsSuspect
    , Recs
FROM inserted;
GO

INSERT  INTO dbo.View1
        ( DataYear
        , ProvId
        , LKId
        , UpdateTime
        , JTimes
        , IsSuspect
        , Recs
        )
VALUES  ( 1
        , 1
        , 1
        , SYSDATETIME()
        , 1
        , 1
        , '1'
        );
GO