1

We are doing a DB2 migration to SQL Server and there are a number of BEFORE inserts/updates that we need to migrate. We can take care of the insert pretty simply by using an INSTEAD OF INSERT by simply using the command "INSERT INTO TableName SELECT * FROM inserted".

However, for the update it is harder as you can't just do a command like "UDPATE TableName SELECT * FROM Inserted. Instead, the only option we have found is to declare variables for each of the incoming columns, and then use those in the UPDATE TableName SET ColumnName = @col1, etc. Unfortunately, this would result in quite a bit of manual work, and I would like to find a more automatable solution.

Some questions: 1) Is there a way you can issue an update using inserted from the trigger, without knowing the specific column information?
2) Is there a way to write a loop in the trigger that would automatically step through the inserted columns, and update those to the database? 3) Is there a way to get access to the original command that caused the trigger? So I can do an EXEC @command and take care of things that way?

Any help would be greatly appreciated!

Thanks! Bob

Bob
  • 97
  • 10

1 Answers1

0
  1. You must specify the column names in an UPDATE
  2. You could loop through the metadata of the target table (in sys.columns) and build an UPDATE statement dynamically, but dynamic SQL executes in its own scope, so it would not be able to access the inserted and deleted tables directly. Although you can work around this by copying the data into local temp tables (#inserted) first, it seems like a very awkward approach in general
  3. There is no way to access the original UPDATE statement

But I'm not sure what you're really trying to achieve. Your question implies that the trigger does the original INSERT or UPDATE anyway without modifying any data. If that's really the case, you might want to explain what the purpose of your trigger is because there may be an alternative, easier way to do whatever it is that it's doing.

I'm also a bit confused by your statement that you have to "declare variables for each of the incoming columns, and then use those in the UPDATE TableName SET ColumnName = @col1, etc". Triggers in SQL Server always fire once per statement, so you normally do an UPDATE with a join to handle the case where the UPDATE is for more than one row.

You might also find the UPDATE() or COLUMNS_UPDATED() functions useful for limiting your trigger code to process only those columns that were really updated.

Community
  • 1
  • 1
Pondlife
  • 15,992
  • 6
  • 37
  • 51
  • Thanks for your reply. We have a large number of DB2 BEFORE UPDATE triggers. These triggers need to be converted to SQL Server. The triggers are basically handling the data validation, so when someone updates the record, the trigger will do some validation checks on the data being sent to the update, then if all is okay, it will allow the update to happen. If the validation fails, it stops the update and returns an error. – Bob Apr 09 '13 at 13:44
  • The issue is that in SQL Server there is no BEFORE UPDATE, so the closest thing I have found is a INSTEAD OF UPDATE. The problem is that the validation happens, and if it passes, nothing happens, if it fails, it returns an error. Therefore I have to do the update myself on the pass state. The problem is I need to find a way to automate as much of the process as possible. Having to know the individual column names for the update makes this far from automatable. I will check out the UPDATE() and COLUMNS_UPDATED() function though. – Bob Apr 09 '13 at 13:47
  • The extra information is helpful, although I don't think it changes my answer much. If I had this requirement I would probably write a script - not necessarily in TSQL - to generate a trigger "template" for each table (by getting the column names from `sys.columns`) including the `UPDATE` statement joining on `inserted` and then just add in my validation code. I assume your validation code is different for each table, so you'll need to maintain the trigger source code for each table anyway. – Pondlife Apr 09 '13 at 14:37
  • As another idea... Instead of trying to produce the UPDATE statement, what if I were to do a delete, then simply insert the rows from the inserted table? There are no identify columns, so I should be able to reinsert the exact record. The question is, how would I know how to identify which rows to delete? For example, since the where clause that was used to select the rows for update isn't available, how do I re-select those rows? Is there a row-level function like UPDATE() that I can query against? – Bob Apr 10 '13 at 15:02
  • That's an interesting idea, but I personally don't like it for the reasons that I mentioned in [this answer](http://stackoverflow.com/questions/10749776/deleting-large-amounts-of-data-from-sql-server-2008). In particular, it could get messy if you use `INSERT` triggers or cascading referential integrity (or if you will in the future), and it makes profiling/debugging and auditing much more difficult. I think in the longer term it would make maintenance and development more difficult. – Pondlife Apr 10 '13 at 15:13
  • Any chance you can elaborate a bit on your proposed solution above, with a script for a trigger template? I may want to go down that path, but I am not sure I understand what you are proposing. – Bob Apr 10 '13 at 15:21
  • There's an example in [this question](http://stackoverflow.com/questions/8873335/how-to-create-generic-sql-server-stored-procedure-to-perform-inserts-into-audit). Although it's possible to do it in TSQL, it may be easier to use a script in PowerShell, Perl or whatever that queries [the system catalog](http://msdn.microsoft.com/en-us/library/ms189783.aspx) for details of column names, primary keys and so on, and then outputs a `CREATE TRIGGER` script. [SMO](http://msdn.microsoft.com/en-us/library/ms162169.aspx) may be useful too if you work with .NET code. – Pondlife Apr 10 '13 at 15:53