question

Debasish avatar image
Debasish asked

Create trigger to restrict data changes

Hi All,

I have a below requirement. Please let me know how it can be taken care weather any trigger or any other way.

Lets say table 1 having id,name,status .Now if status value is already 'Closed' for a row then it will not allow to update that row.For any other status it can be allow

triggers
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Oleg avatar image
Oleg answered

If you would like to do it by the means of the update trigger then please keep in mind that the trigger fires ONCE per update statement, not once per updated row. In other words, if you issue an update on all rows in the table, something like this:

update table1 set [name] = [name] + 'X';

then this statement will affect all rows in the table, but the trigger for update will fire only once. This means that if you include the code to not update the rows where the status = 'Closed' then you will inevitably end up either with the complete failure of the update statement for all rows and some error message or with the risk of running into recursive call of the trigger.

If you really do need to simply (and silently) skip the rows where status = 'Closed' while allowing all other affected rows to be updated successfully without any errors or recursion then you can try INSTEAD OF UPDATE trigger. This trigger will NOT cause a recursive call to itself even though its code might include the UPDATE statement.

In your question you mention that there is a table named table1 with 3 columns. I will assume that the id column is the one used to uniquely identify the records in the table, so the only 2 updateable columns are [name] and [status]. In this case, the INSTEAD OF UPDATE trigger should do the trick:

create trigger dbo.Table1ConditionalUpdateTrigger on dbo.Table1 instead of update 
as
    set nocount on;
    update t
    set
        [name] = i.[name], [status] = i.[status]
        from dbo.Table1 t inner join inserted i
            on t.id = i.id
        where isnull(t.[status], '') <> 'Closed';
go 

This way, when the update statement is issued, it will fire the trigger before the update operation actually takes place. At this time, it will issue the update statement which excludes the rows where status is already equal to Closed. Because the trigger is INSTEAD OF, the update statement it itself issues will not cause a recursive firing of the trigger.

Say, for example, there are 100 rows in the table now, 50 of which already have the Closed status. If you issue an update statement

update table1 set [name] = [name] = 'X';

then such statement (without any WHERE clause) will "affect" all 100 rows in the table. The trigger will cause only those rows to be actually updated which don't have the status = 'Closed' yet meaning that only 50 rows will be updated. You will see a message in your query results pane reading (100 rows affected) because the update statement included all 100 rows to update, it is just the trigger excluded some of them because of their status. The records affected is still 100 as far as the engine is concerned.

Hope this helps.

Oleg

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.