We have a table that needs to be locked down in a specific way 以及we are looking for the best (reliability is first priority, performance is second) way to accomplish it. The rules involving this table are that new records can be added freely but once they have been then, with one exception, they should never be changed. There is going to be one valid update comm以及allowed for records in this table, all other updates or deletes should be blocked. Here is a rough example of the approach we have worked out so far, are there any good ways to improve it?

Table looks like this:

CREATE TABLE [dbo].[Table_1](
    ID int IDENTITY(1,1) PRIMARY KEY NOT NULL
    ,data1 varchar(64) NULL
    ,data2 varchar(64) NULL
    ,data3 int NULL
    ,data4 bit NULL
    ,ModifiedBy VARCHAR(32)
    ,ModifiedDtTm DATETIME
)

The only allowable update will be via a proc which resembles this:

UPDATE dbo.Table_1
SET 
    data4 = 1
    ,ModifiedBy = @User
    ,ModifiedDtTm = GETDATE()
WHERE ID = @ID

What we have in mind to lock the table is to create these two triggers:

CREATE TRIGGER [dbo].[Table_1_UpdtLock] 
   ON  [dbo].[Table_1]
   INSTEAD OF UPDATE
AS 
BEGIN
    IF COLUMNS_UPDATED() = 0x70
        UPDATE dbo.Table_1
        SET 
            data4 = I.data4
            ,ModifiedBy = I.ModifiedBy
            ,ModifiedDtTm = I.ModifiedDtTm
        FROM dbo.Table_1 AS T
            INNER JOIN INSERTED AS I
                ON T.ID = I.ID
        WHERE I.data4 = 1
    ELSE
    BEGIN
        RAISERROR ( Table is locked. , 16, 0)
        ROLLBACK
    END
END

以及

CREATE TRIGGER [dbo].[Table_1_DelLock]    
   ON  [dbo].[Table_1]
   INSTEAD OF DELETE
AS 
BEGIN
      ROLLBACK
      RAISERROR ( Table is locked. , 16, 0)
END

This has not been implemented yet 以及we are trying to make sure we have it right the first time since there are potential legal ramifications if this data gets corrupted or lost.

任何人都能够看到任何漏洞,使某个人能够将数据推入本表? 我在这一点上知道的唯一一点是,可以在不启动删除的触发点的情况下缩小这一表。 我现在要回答的最佳答案是确定科索沃统计局对这个表格的参考。

Any potential performance problems that st以及out?

建议采取更好的做法?

感谢援助。

http://www.un.org。

I should note that access to the database will be restricted as tightly as we can make it. However there is always an account with DBO 以及our SAs generally don t know much about what we are doing with any given database. I know that people with these permissions can circumvent anything we put in place, the goal at this level is to prevent accidents. I should also note that we want it to throw an error if someone attempts an illegal update, it should not fail silently or perform a partial update on the allowed fields.