Menu
I have the following setup:
CREATE TABLE dbo.Licenses ( Id int IDENTITY(1,1) PRIMARY KEY, Name varchar(100), RUser nvarchar(128) DEFAULT USER_NAME() ) GO CREATE VIEW dbo.rLicenses AS SELECT Name FROM dbo.Licenses WHERE RUser = USER_NAME() GO When I try to insert data using the view...
INSERT INTO dbo.rLicenses VALUES ('test') an error arises:
Cannot insert the value NULL into column Id, table master.dbo.Licenses; column does not allow nulls. INSERT fails. Why doesn't the auto increment of the identity column work when trying to insert using the view and how can I fix it?
Scenario is:
The different users of the database should only be able to work with their own rows in that table. Therefore I am trying to use the view as a kind of security by checking the username. Is there any better solution?
793
You can insert data through a single-table view if you have the Insert privilege on the view. To do this, the defining SELECT statement can select from only one table, and it cannot contain any of the following components: DISTINCT keyword. GROUP BY clause.
We cannot insert or update data using view. The view is a virtual table. We can do those action, but it's effect on real table data too. View is like a virtual table which enable us to get information of multiple tables.
What about naming your column?
INSERT INTO dbo.rLicenses (name) VALUES ('test') It's been years since I tried updating via a view so YMMV as HLGEM mentioned.
I would consider an "INSTEAD OF" trigger on the view to allow a simple INSERT dbo.Licenses (ie the table) in the trigger
109
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
