I have SQL Server 2016. I have two tables with the same structure, one contains historical data, and the other one contains current data.
I have a view that programs use, that returns the content of both tables.
CREATE TABLE A (int Id IDENTITY(1,1) NOT NULL, othercolumns, primary key, ect.) CREATE TABLE B (int Id NOT NULL, othercolumns, primary key, ect.) CREATE VIEW MyView AS SELECT * FROM A UNION ALL SELECT * FROM B CREATE TRIGGER TR_INSTEADOFINSERT_ON_MyView on MyView INSTEAD OF INSERT AS BEGIN INSERT INTO A (columns) SELECT data FROM inserted END
I was wondering if the view gets re-created everytime an insert is done in the view. Because if I check in the sys.dm_exec_sessions and sys.db_exec_requests, I always see the "CREATE TRIGGER TR_INSTEADOFINSERT" in the query text beign executed.
Now I know that it's taking a long time because it's a big table and I have a lot of activity on the server. But I was just wondering why I keep seeing the "CREATE TRIGGER" in the sql_text of the query being executed? Is it re-created or not? What's the most efficient way to do this? Should I modify the programs to reference the table directly when inserting?