Sql-server – Copy a row from a table to another with the same columns but different order in a trigger

sql servert-sql

I have two tables:

Document

Columns: {DocumentColumns}

DocumentVersion

Columns: DocumentVersion_id(autoincrement), {DocumentColumns}

{DocumentColumns} are the same in both tables, but their order can differ.

I need that any change that happens in Document, must be added in DocumentVersion, so I created this trigger :

Create trigger copyDocumentVersionTrigger on dbo.Document after insert,update as 
insert into [DocumentVersion] select null as DocumentVersion_id,* from inserted

When I want to execute this request I get :

Msg 206, Level 16, State 2, Procedure copyDocumentVersionTrigger, Line 1 [Batch Start Line 0]
Operand type clash: bit is incompatible with uniqueidentifier

Because the third column in Document is a bit, and the third one in DocumentVersion is a unique identifier ( and they are totally different columns, that only means that t-sql doesn't care about the named columns in the select, it only cares about the positions).

{DocumentColumns} can be changed dynamically after the creation of the trigger, so I can't just put explicitly the names.

Is there any simple solution to achieve this?

Best Answer

Apparently we don't consider it "simple" to obtain column names of the tables from sys.columns or from Information_Schema.Columns (which handily has a table name column), and then.... hmm, yes, if you construct a "dynamic SQL" statement to call with EXEC() then it won't have access to the [inserted] and [deleted], so you need to copy the row key column to a temporary table... I suppose that isn't simple. Unless you are doing it one row at a time - then the row key column, is just one variable, and by calling sp_executesql with the appropriate parameter... hmm, again, today may be a headache day.

However... if that's a Microsoft SQL Server error message, then you can again use dynamic SQL to create view objects on one or both of the tables that simply have the column order changed - and you can write in your trigger (I think) if you want,

INSERT DocumentVersionView SELECT * FROM inserted;

INSERT DocumentVersionView SELECT dv.* FROM DocumentView dv JOIN inserted di ON blah blah blah

Whichever is required. The INSERT will write to the table in the view.

Update: "Of course" DocumentVersionView doesn't have column DocumentVersion_id; the table column gets assigned automatically by your auto-increment.

Related Question