1

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?

1

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.

  • I used dynamic sql, you can copy Inserted table in a temporary variable. – yeska Jun 16 '17 at 15:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.