Trigger: copy inserted records to another table

[ettienne]

Please forgive me if this has been asked, the search function is currently under maintenance.
Here is what I want to do: copy new records inserted in table A to table B.

This is what I have tried:

CREATE TRIGGER [Append Sales] ON dbo.A 
FOR INSERT
AS
INSERT INTO dbo.B ( CUSTOMER, ITEM, YR, PERIOD, TRANDATE, SALESPER, LOCATION, CATEGORY, QTYSOLD, COST, SALES )
SELECT CUSTOMER, ITEM, YR, PERIOD, TRANDATE, SALESPER, LOCATION, CATEGORY, QTYSOLD, COST, SALES
FROM dbo.A

When a new record is inserted in table A all existing records (but not the new record) is copied to table B.
I tried using a WHERE in the query, but that made no difference.

I am obviously missing something here, but cannot figure out what.

 

[ptheriault]

You want to select from the INSERTED table. This will give you only the new records.

[COLOR=blue]CREATE[/color] [COLOR=blue]TRIGGER[/color] [Append Sales] [COLOR=blue]ON[/color] dbo.A
[COLOR=blue]FOR[/color] [COLOR=blue]INSERT[/color]
[COLOR=blue]AS[/color]
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] dbo.B ( CUSTOMER, ITEM, YR, PERIOD, TRANDATE, SALESPER, LOCATION, CATEGORY, QTYSOLD, [COLOR=blue]COST[/color], SALES )
[COLOR=blue]SELECT[/color] CUSTOMER, ITEM, YR, PERIOD, TRANDATE, SALESPER, LOCATION, CATEGORY, QTYSOLD, [COLOR=blue]COST[/color], SALES
[COLOR=blue]FROM[/color] INSERTED

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[ettienne]

Thanks, I stumbled across that after much reading- RTFM.

The correct syntax is dbo.INSERTED