How to insert a record into entity based on an action on another entity?

Question

I have two disconnected models. The first one is Event, which is in simplified form below:

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

    }

The second is the Tickets model, derived from the Event SQL table. The Tickets model shows open tickets only:

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

The Tickets model is below:

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

Now, I want on clicking on the ActionLink on the Tickets view to insert a new event into the Event SQL database. Something like clicking on the:

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

Would fire up the following action in the Tickets controller:

[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);
    }

The issue is that because the Tickets is a SQL view, it does not accept any CREATE/UPDATE/DELETE actions.

Is there a way around it? Thanks

Answer 1

I was able to resolve the issue by introducing SQL table triggers on INSERT and DELETE actions.

First, the Ticket entity (shows unresolved tickets) is emptied and repopulated as a table every time a new insert happens into the Event Entity:

    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

Second, I am adding a trigger on the Tickets table to insert a line into the dbo.Events table INSTEAD of DELETE action

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

Finally, in the Index view a submit button is added to activate the DELETE action:

 @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>
    }