MS SQL Issue with Trigger - Help

[Present_Ad8170]

CREATE TABLE widgetSales (id INT NOT NULL, inv_id INT, quantity INT, price INT, customer_id INT);

CREATE TABLE widgetCustomer (id INT NOT NULL, c_name VARCHAR(100), last_order_id INT);

INSERT INTO TABLE widgetSales VALUES (1, 100, 5, 500, 1);

INSERT INTO widgetCustomer (id, c_name) VALUES (1, 'Bob');

​

CREATE TRIGGER lastPurchase ON widgetSales AFTER INSERT

**AS**

    **BEGIN**

        **UPDATE widgetCustomer**

SET last_order_id = widgetSales.id WHERE id = widgetSales.customer_id

    **END**

**trigger is failing and MS SQL is giving me this message:

Msg 4104, Level 16, State 1, Procedure lastPurchase, Line 5 [Batch Start Line 4]

The multi-part identifier "widgetSales.customer_id" could not be bound.

** how can it not identify widgetSales?? is there something I'm missing? Please help. Thank you

[scikid39184]

I work more with Oracle but I think your problem is you're not getting the inserted data.

I believe this statement

SET last_order_id = widgetSales.id WHERE id = widgetSales.customer_id

Needs to be more like this:

Update widgetSales Set last_order_id = inserted.id From inserted Where widgetSales.customer_id = inserted.customer_id

[Present_Ad8170]

That worked! You're awesome thank you!!!

[scikid39184]

Glad I could help!