Hi,
I have a user defined table where I will occassionally insert some data. I do not want any entry in this table after insert to be deleted or updated. No deletions or updates in existing entries are allowed. This table will just get data by insert statements. How do I work on this issue? Should I write a Instead Of trigger for update and delete? Please suggest the best way to achieve this.
Basically you want limited DDL access to the table or, in other words, you want read and write but no delete or update. This can easily be accomplished (in SQL Server 2005, 2008 or above) by definining a custom database role which applies to a specific schema (BEST) or explicitly GRANTing or DENYing permissions on an object (not demonstrated in my code below).
--this example works on SQL Server 2005, 2008 or above
--be sure to replace MyUser, MySchema, MyTable and MyDB with the correct names
--NOTE: this solution will not prevent someone with sysadmin privilege from performing the DELETE or UPDATE
USE [MyDB]
GO
CREATE ROLE [MyTable_InsertOnly]
GRANT INSERT ON SCHEMA::[MySchema] TO [MyTable_InsertOnly]
DENY UPDATE, DELETE ON SCHEMA::[MySchema] TO [MyTable_InsertOnly] --in case they have permissions mapped another way DENY will override
GO
CREATE USER [MyUser] FOR LOGIN [MyUser]
EXEC sp_addrolemember N'MyTable_InsertOnly', N'MyUser'
Yes, this is one of the few cases where instead of triggers make sense.
It depends. You can use an AFTER TRIGGER to, in case the statement is used in a composable dml statement, and you want complete statement to fail if the table is touched.
