In SQL Server 2008, I have a scenario where I have a table with complex validation upon insert / update. This includes needing to temp convert an XML input into a table in order to validate its data against a permanent table.
在SQL Server 2008中,我有一个场景,我在插入/更新时有一个包含复杂验证的表。这包括需要将XML输入临时转换为表,以便针对永久表验证其数据。
However, I also have the scenario where I will often update simple integer columns that require no validation. From what I have read here, it seems that SQL Server is going to return the entire row in the temp in-memory "inserted" table, not just the affected columns, when I perform an update. If this is so, then it means for every simply integer update I perform, complex XML validation will be needlessly done.
但是,我还有一个场景,我经常会更新不需要验证的简单整数列。从我在这里看到的,当我执行更新时,似乎SQL Server将返回临时内存中“插入”表中的整行,而不仅仅是受影响的列。如果是这样,则意味着对于我执行的每个简单整数更新,将不必要地进行复杂的XML验证。
Do I understand this correctly and if so, how do I get around this short of requiring inserts / updates via a stored proc?
我是否正确理解这一点,如果是这样,我如何通过存储过程来避免需要插入/更新?
1 个解决方案
#1
3
Yes, first off, the triggers will fire for EVERY INSERT or UPDATE operation - you cannot limit that to only fire when certain columns will be affected. You can check inside the trigger to see whether or not certain columns have been affected and make decisions based on that - but you cannot prevent the trigger from firing in the first place.
是的,首先,触发器将针对每个INSERT或UPDATE操作触发 - 您不能将其限制为仅在某些列受到影响时触发。您可以检查触发器内部以查看某些列是否受到影响并根据该列做出决定 - 但您无法阻止触发器首先触发。
And secondly, yes, when the trigger fires, you will have ALL columns of the underlying table in the INSERTED and/or DELETED pseudo tables.
其次,是的,当触发器触发时,您将在INSERTED和/或DELETED伪表中拥有基础表的所有列。
One way you might want to change this is by moving the large XML column into a separate table and put that big heavy XML validation trigger only on that table. In that case, if you update or insert into the base table only, you'll get less data, less validation logic. Only when you insert or update into the XML table, you'll get the whole big validation going.
您可能希望更改此方法的一种方法是将大型XML列移动到单独的表中,并将该大型XML验证触发器仅放在该表上。在这种情况下,如果仅更新或插入基表,您将获得更少的数据,更少的验证逻辑。只有当您插入或更新到XML表中时,您才能获得全面的验证。
#1
3
Yes, first off, the triggers will fire for EVERY INSERT or UPDATE operation - you cannot limit that to only fire when certain columns will be affected. You can check inside the trigger to see whether or not certain columns have been affected and make decisions based on that - but you cannot prevent the trigger from firing in the first place.
是的,首先,触发器将针对每个INSERT或UPDATE操作触发 - 您不能将其限制为仅在某些列受到影响时触发。您可以检查触发器内部以查看某些列是否受到影响并根据该列做出决定 - 但您无法阻止触发器首先触发。
And secondly, yes, when the trigger fires, you will have ALL columns of the underlying table in the INSERTED and/or DELETED pseudo tables.
其次,是的,当触发器触发时,您将在INSERTED和/或DELETED伪表中拥有基础表的所有列。
One way you might want to change this is by moving the large XML column into a separate table and put that big heavy XML validation trigger only on that table. In that case, if you update or insert into the base table only, you'll get less data, less validation logic. Only when you insert or update into the XML table, you'll get the whole big validation going.
您可能希望更改此方法的一种方法是将大型XML列移动到单独的表中,并将该大型XML验证触发器仅放在该表上。在这种情况下,如果仅更新或插入基表,您将获得更少的数据,更少的验证逻辑。只有当您插入或更新到XML表中时,您才能获得全面的验证。