I'm trying to create a trigger to insert a row from tblSet to tblSetHistory when a column is updated:

USE smallcha
IF EXISTS (SELECT name FROM sysobjects
  WHERE name = 'tblSet_update' AND type = 'TR')
    DROP TRIGGER tblSet_update
GO
  CREATE TRIGGER tblSet_update ON tblSet
    FOR UPDATE AS
      IF UPDATE(WFState)= 1
        INSERT INTO tblSetHistory
          SELECT * FROM INSERTED tblSet
GO

which returns "Server: Msg 170, Level 15, State 1, Procedure tblSet_update, Line 3. Line 3: Incorrect syntax near '='." What am I doing wrong?

IF UPDATE(WFState) returns TRUE, but you are checking for 1 and use BEGIN...END to group your statements, in case you have separated those lines of INSERT statement into 2 lines.

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection

Thank you, happygv. I removed "= 1" from what Server states is my line 3.

Now I get ""Server: Msg 8101, Level 16, State 1, Procedure tblSet_update, Line 4. An explicit value for the identity column in table 'tblSetHistory' can only be specified when a column list is used and IDENTITY_INSERT is ON." Does this mean I can't insert the entire row from tblSet as is?

Hi there,

Your tblSetHistory table has got an IDENTITY column in it, into which you are trying to insert data using a select statement followed in your Trigger. So to get that work you must use it this way.
If you want the SQL server to handle adding value to the INDENTITY column in that table, you got to select only the columns other thatn IDENTITY column for adding in your Insert statement.

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection