'Is there any way to track changes from views in MS Sql Server?
I'm looking for how to track changes from a view in MS Sql-Server 2012. And, the role of the log-in user is Public. So, it's hard to do it.
For example, Assuming that there is the schema.
CREATE TABLE [dbo].[USER_CREDENTIAL](
[USERID] [nvarchar](48) NOT NULL,
[VALID_FROM] DATETIME NULL,
[EXPIRED_AT] DATETIME NULL,
[CREDENTIAL_ID] int NOT NULL,
CONSTRAINT [UNIQUE_USERID] PRIMARY KEY CLUSTERED( [USERID] ASC)
) ;
CREATE VIEW [VIEW_OF_USER_CREDENTIAL] as
SELECT * FROM dbo.[USER_CREDENTIAL];
It can be only permitted to access the view. The view will be changed when some data is inserted/updated/deleted from the USER_CREDENTIAL table. I will do query to the view.
I saw the document. I tried that, but the target to track should be the data table and the login user is lack of the role. I got the error message.
Object 'foo' is of a data type that is not supported by the CHANGETABLE function. The object must be a user-defined table.
I tried the following. I added the temporary table and the trigger which make changed-data be inserted to the temporary table when the view is changed. But, it was also failed because it was permission denied.
CREATE TABLE dbo.[CHANGES_FROM_A_VIEW] (
[VERSION] [int] IDENTITY(1,1) NOT NULL,
[USERID] [nvarchar](48) NOT NULL,
CONSTRAINT [UNIQUE_VERSION] PRIMARY KEY CLUSTERED ( [VERSION] ASC)
)
CREATE TRIGGER [SOMETHING_CHANGED] ON dbo.[VIEW_OF_USER_CREDENTIAL] ...
ALTER DATABASE database_name
SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 2 DAYS,AUTO_CLEANUP = ON)
ALTER TABLE [CHANGES_FROM_A_VIEW]
ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON)
SELECT * FROM CHANGETABLE(CHANGES dbo.CHANGES_FROM_A_VIEW, 0) AS C
Anyone knows any way to solve this?
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
Solution | Source |
---|