Msg 4104, Level 16, State 1, Procedure AddItemToCurrentItems, Line 11 The multi-part identifier "INSERTED.IsActive" could not be bound

Question

Hi,

I have following trigger written for my application which gives me this error "Msg 4104, Level 16, State 1, Procedure AddItemToCurrentItems, Line 11 The multi-part identifier "INSERTED.IsActive" could not be bound."

My intension with this trigger is to copy the inserted data in table "Item" to table "CurrentItems" but only if the bit field in Items table is set to True i.e. 1. Both tables have the same table structure

CREATE TRIGGER AddItemToCurrentItems 
   ON ITEMS FOR INSERT
--,UPDATE
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for trigger here
    IF INSERTED.IsActive = 1
    BEGIN
       INSERT INTO CurrentItems
           SELECT * FROM INSERTED
    END
END

Any ideas, what I am doing wrong here.

Answer 1

You are missing a reference to the INSERTED table in you if clause, would need to look something like this:

IF exists (select * from inserted i where i.IsActive = 1) BEGIN
...

NOTE however that the inserted/updated tables can always contain multiple records, just like any SQL table, so you'll need to be sure your logic handles multi-record possibilities, for example your insert within your IF block should likely contain an additional where clause, something like this:

IF exists (select * from inserted i where i.IsActive = 1) 
BEGIN
    INSERT CurrentItems SELECT * FROM INSERTED where IsActive = 1
END

Or, you could just leave out the IF block alltogether and do something like this:

INSERT CurrentItems SELECT * FROM INSERTED where IsActive = 1

NOTE that you probably should also consider including a column list in both your insert and select statements...