For several tables with identifier fields, we implement a row-level security scheme using views and instead of triggers for these views. Here is a simplified example structure:
-- Table CREATE TABLE tblItem ( ItemId int identity(1,1) primary key, Name varchar(20) ) go -- View CREATE VIEW vwItem AS SELECT * FROM tblItem -- RLS Filtering Condition go -- Instead Of Insert Trigger CREATE TRIGGER IO_vwItem_Insert ON vwItem INSTEAD OF INSERT AS BEGIN -- RLS Security Checks on inserted Table -- Insert Records Into Table INSERT INTO tblItem (Name) SELECT Name FROM inserted; END go
If I want to insert a record and get its identity, before implementing RLS, instead of a trigger, I used:
DECLARE @ItemId int; INSERT INTO tblItem (Name) VALUES ('MyName'); SELECT @ItemId = SCOPE_IDENTITY();
Using the SCOPE_IDENTITY () trigger no longer works - it returns NULL. I have seen suggestions for using the OUTPUT clause to get my identity back, but I can't get it to work the way I need it. If I put an OUTPUT clause in a view insert, nothing is entered into it.
-- Nothing is added to @ItemIds DECLARE @ItemIds TABLE (ItemId int); INSERT INTO vwItem (Name) OUTPUT INSERTED.ItemId INTO @ItemIds VALUES ('MyName');
If I put the OUTPUT clause in a trigger in an INSERT statement, the trigger will return the table (I can view it from SQL Management Studio). I can't seem to capture it in the calling code; either by using the OUTPUT clause of this call, or by using SELECT * FROM ().
-- Modified Instead Of Insert Trigger w/ Output CREATE TRIGGER IO_vwItem_Insert ON vwItem INSTEAD OF INSERT AS BEGIN -- RLS Security Checks on inserted Table -- Insert Records Into Table INSERT INTO tblItem (Name) OUTPUT INSERTED.ItemId SELECT Name FROM inserted; END go -- Calling Code INSERT INTO vwItem (Name) VALUES ('MyName');
The only thing I can think of is to use the IDENT_CURRENT () function. Since this does not work in the current area, the problem arises of simultaneous inserts at the same time and ruin it. If the whole operation is wrapped in a transaction, will this prevent the concurrency problem?
BEGIN TRANSACTION DECLARE @ItemId int; INSERT INTO tblItem (Name) VALUES ('MyName'); SELECT @ItemId = IDENT_CURRENT('tblItem'); COMMIT TRANSACTION
Does anyone have any suggestions on how to make this better?
I know people who will read this and say: "Triggers are evil, do not use them!" Although I value your beliefs, please do not offer this "offer."