Trigger to insert into a table a “SELECT SUM” from

问题:

I have these 2 tables:

The data in the table a AUX has duplicated dates, and the table a, must have the dates with no duplicates, BUT adding the total of the duplicated dates.

ej. 05/01/2017 = 123 + 123 + 123.

I'm thinks that a trigger should do the job when the data in the table a aux has new data.

回答1:

CREATE TRIGGER [dbo].[trgAfterInsert] ON [dbo].[Table_a_aux] 
After Insert
AS
BEGIN

Declare @dDate as Date;
Declare @iTotal as int;

Select 
    @dDate = i.[date],
    @iTotal = i.total
From inserted i;


IF EXISTS (Select [date] from Table_a where [date] = @dDate) 
BEGIN
Update Table_a 
    SET total = total + @iTotal
WHERE
    [date] = @dDate
END
ELSE 
BEGIN
INSERT INTO Table_a ([date],total) Values (@dDate,@iTotal)
END