Submit Your Suggestions for Vista

The SQL MERGE statement has been around since 2008 (10 years ) and is a great help for set based operations when inserting/updating rows. Updating your triggers to not block it would greatly be appreciated!

What is SQL Merge?

SQL Merge is a command that allows a programmer to do multiple inserts AND deletes in one command, instead of using separate commands for inserts and deletes.

How does using [select @numrows = @@rowcount] in VP's triggers block SQL MERGE?

Because SQL MERGE can do inserts, updates, and deletes all at the same time, the @@rowcount variable contains the TOTAL number of rows affected. Imagine for a moment that we are inserting 3 records, deleting 2, and updating 1. That's a total of 6 records affected. Thus, @@rowcount will be equal to 6. Let's step through the process as it hits a simple UPDATE trigger.

• As the update trigger starts, @@rowcount equals 6. So the update trigger sets @numrows = 6.
• Next up, the update trigger checks "Is @numrows different than the count of rows in [inserted] that have a valid company number?".
• This would turn into something like "Is 6 equal to 4?" (4 = 3+1, 3 updated + 1 inserted).
• Obviously, 4 does not equal 6, thus the UPDATE trigger will throw an error like "Invalid field data!!".

What about a INSERT trigger?

• As the insert trigger starts, @@rowcount = 6, so trigger sets @numrows = 6.
• Next up, the update trigger checks "Is @numrows different than the count of rows in [inserted] that have a valid company number?".
• This would turn into something like "Is 6 equal to 4?" (4 = 3+1, 3 updated + 1 inserted).
• And the same error would happen.

What WOULD happen if Viewpoint fixes their triggers:

• As the INSERT trigger starts, it sets @numrows = (select count(*) from inserted) , would be 4. @numrows = 4.
• Next up, the update trigger checks "Is @numrows different than the count of rows in [inserted] that have a valid company number?".
• This would turn into something like "Is 4 equal to 4?" (4 = 3+1, 3 updated + 1 inserted).
• YES!!! YES!!! Exactly what we want.

What would it take for Viewpoint to fix their triggers?

Go through ALL their table triggers and replace [select @numrows = @@rowcount] with

• For all INSERT triggers, use [select @numrows = select count(*) from inserted where KeyID is null], then go the rest of the trigger, and anywhere where the [inserted] table is referenced, make sure to add the same WHERE condition.
• For all UPDATE triggers, do the same thing but use [where KeyID is NOT null] throughout the trigger. This would make sure that each time it interacts with the [inserted] table, it is only selecting rows that are UPDATE rows.
• For all DELETE triggers, do a join between the [inserted] and [deleted] tables, and count rows that exist ONLY in the [deleted] table. Reason: An UPDATE operation stores copies of the rows BEFORE update in the [deleted] table, then AFTER the update in the [inserted] table. Thus we want to make sure in the DELETE trigger to only consider rows that are NOT also in the inserted table.

Why go to all this work?

So that we can start using MERGE startements in our stored procedures. This is particularly useful when importing large amounts of data and can seriously speed things up. Using SET BASED OPERATIONS is always recommended over LOOPS whenever possible, by pretty much every SQL performance guru I've run across. 

Viewpoint? What are your long term plans with the database?