如何根据对另一个实体的操作将记录插入到实体中？

Question

我有两个断开连接的模型。第一个是 Event，其简化形式如下：

public partial class Event
    {
        public int EventID { get; set; }
        public string Subject { get; set; }
       ...
        public string Status { get; set; }

    }

第二个是 Tickets 模型，源自 Event SQL 表。票证模型仅显示未结票证：

with ds as (select distinct subject from events 
except 
select distinct subject from events where status like 'Closed%')

    select isnull(cast(ROW_NUMBER() OVER(ORDER BY [subject] ASC)  as int),0) as [TicketId], subject as Ticket from ds

票证模型如下：

public partial class Tickets
{
    public int TicketId { get; set; }
    public string Ticket { get; set; }
}

现在，我想单击票证视图上的 ActionLink 将新事件插入到事件 SQL 数据库中。单击以下内容：

@Html.ActionLink("Resolve", "Create", new { id=item.TicketId })

将在 Tickets 控制器中启动以下操作：

[HttpPost]
    [ValidateAntiForgeryToken]
    public async Task<ActionResult> Create([Bind(Include = "Subject")] Event events)
    {
        if (ModelState.IsValid)
        {
            db.Events.Add(events);
            await db.SaveChangesAsync();
            return RedirectToAction("Index");
        }

        return View(events);
    }

问题在于，由于 Tickets 是 SQL 视图，因此它不接受任何 CREATE/UPDATE/DELETE 操作。

有办法解决吗？谢谢

Answer 1

我能够通过在 INSERT 和 DELETE 操作上引入 SQL 表触发器来解决该问题。

首先，每次在事件实体中发生新插入时，工单实体（显示未解决的工单）都会被清空并重新填充为表：

    CREATE TRIGGER [dbo].[UpdateEvent] 
       ON   [dbo].[Events]
       AFTER INSERT
    AS 
    
    BEGIN
        ...
    
        IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Tickets' AND TABLE_SCHEMA = 'dbo')
    
       CREATE TABLE [dbo].[Tickets](
        [TicketId] [int] NOT NULL,
        [subject] [nvarchar](300) NOT NULL,
     CONSTRAINT [PK_OpenTickets] PRIMARY KEY CLUSTERED 
    (
        [TicketId] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY];
    
...
    
    
    with ds as (select distinct subject from events 
    except 
    select distinct subject from events where status like 'Closed%')
    insert into dbo.Tickets SELECT isnull(cast(ROW_NUMBER() OVER(ORDER BY [subject] ASC)  as int),0) as [TicketId], subject  from ds;
    
    
    delete from dbo.Tickets where subject like 'test%';
    
...
    
    END

其次，我在工单表上添加一个触发器，以将一行插入到 dbo.Events 表中DELETE 操作的

CREATE TRIGGER [dbo].[ResolveTickets] ON [dbo].[Tickets] 
INSTEAD OF delete 
AS BEGIN 
declare @ticket nvarchar(max)= (select [subject] from deleted ins) 

INSERT INTO dbo.Events ( Subject, Description, [Start], [End], Status, Hours) 
SELECT @ticket,'Resolved and Closed',cast(getdate() as date), dateadd(dd,1, cast(getdate() as date)),'Closed',0.00 

END

最后，在 Index 视图中添加一个提交按钮来激活 DELETE 操作：

 @foreach (var item in Model)
    {
        <tr>
            <td>
                @Html.DisplayFor(modelItem => item.subject)
            </td>
            <td>
                <div>


                    @using (Html.BeginForm("Delete", "Tickets", new { id = item.TicketId }, FormMethod.Post, null))
                    {
                        @Html.AntiForgeryToken()

                    <div class="form-actions no-color">

                        **<button type="submit" name="submitButton" value="Delete" style="border:none">Close the Ticket</button>**

                        <style>
                            button {
                                background-color: rgba(255,255,255,0);
                                color: cornflowerblue;
                            }
                        </style>


                    </div>
                    }
                </div>


               
            </td>
        </tr>
    }