Sql server trigger if update multiple columns

In the case of SQL Server DML triggers there are two virtual tables during the execution of the trigger that holds the data being affected by the trigger execution. Those tables are named inserted and deleted and have the same table structure as their base table.

Something to keep in mind is that the inserted and deleted tables are not always available together i. You will find more information about these tables in the following tip. Additionally, for the inserted and deleted tables with DML triggers there are two functions available that you can call inside the trigger code.

It receives a column name as a parameter, which of course must be a column present on the underlying table or view. First you have to create the test table by running the script on the next code section. As you may see in the code above, there is one IF block for each column. What do you think will happen when we try to update that column? Wait and see. This is not a bug; it is a marked as "by design" by Microsoft. This function is used to know the inserted or updated columns of a table or view.

As you may know, each byte has 8 bits so the length of the VARBINARY stream in bytes is the smallest integer greater than, or equal to the number of columns divided by 8. Finally the smallest integer greater than or equal to 3. The bytes of the VARBINARY stream are ordered from left to right, so if you have a table with 15 columns the first byte from the left will contain the bitmask value for columns 1 to 8 the first eight columns. How can I modify this trigger to make it run on a view? Thanks EDIT 1: I get this error ; View or function 't' is not updatable because the modification affects multiple base tables.

Improve this question. Update t. ColName twice? Add a comment. Active Oldest Votes. I think that your error message is normal. Improve this answer. FunctionName2 i. PrimaryId END. BeanFrog BeanFrog 2, 10 10 silver badges 25 25 bronze badges. Sign up or log in Sign up using Google. Sign up using Facebook. Sign up using Email and Password. There is also a simpler solution, but it works only in cases similar to yours. The trigger body in your case consists of a single statement. On a different note, your UPDATE statement may have a flaw: as currently written, it will update the piece column in every row with the same row count — that of the category matching the inserted row.

If the table has multiple rows, each for a different category, you probably need to introduce a filter on new. I wanted to update status of table1 from 'Requested' to 'Paid' whenever any item added into field items of table2.