触发器，SQL SERVER 2008

Question

我有两张桌子

PaymentData
Ser   Customerid Totalpaid
1.    AGP001     2400
2.    AGP002     1000
3.    AGP003     1500

Receipt
Receipt#    Customerid  Paid
1.          AGP001      1200
2.          AGP001      1200

我想在Receipt表上创建一个触发器，触发器将在更新PaymentData表的totalpaid字段的insert、update和delete操作时触发。每次针对某个customerid插入或更新新的收货记录时，该客户的totalpaid字段也会更新。触发器应执行以下操作。

Update PaymentData.totalpaid = sum(Recipt.paid) 
where Receipt.customerID = PaymentData.customerID

Answer 1

我想你需要一些像这样的触发器：

CREATE TRIGGER dbo.OnReceiptUpdate
   ON  dbo.Receipt
   AFTER INSERT,DELETE,UPDATE --operations you want trigger to fire on
AS 
BEGIN   
    SET NOCOUNT ON;

    DECLARE @customer_id VARCHAR(10)

    SET @customer_id= COALESCE
                        (
                            (SELECT customer_id FROM inserted), --table inserted contains inserted rows (or new updated rows)
                            (SELECT customer_id FROM deleted) --table deleted contains deleted rows

                        )

    DECLARE @total_paid DECIMAL

    SET @total_paid = 
            (
                SELECT SUM(paid) 
                FROM Receipt
                WHERE customer_id = @customer_id
            )

    UPDATE PaymentData
        SET total_paid = @total_paid            
    WHERE customer_id = @customer_id

    IF @@ROWCOUNT = 0               --if nothing was updated - you don't have record in PaymentData, so make it
        INSERT INTO PaymentData (customer_id, total_paid)
            VALUES (@customer_id, @total_paid)  

END
GO

请记住，这并不适用于多次更新/删除/插入--这只是你需要如何做的一个例子

Answer 2

使用multiply updates、inserts或deletes尝试此触发器。

CREATE TRIGGER [dbo].upd_PaymentData ON dbo.Receipt
FOR INSERT, UPDATE, DELETE
AS
IF @@ROWCOUNT = 0 return
SET NOCOUNT ON;
DECLARE @actionTable nvarchar( 10),
        @insCount int = (SELECT COUNT(*) FROM inserted),
        @delCount int = (SELECT COUNT(*) FROM deleted)
SELECT @actionTable = CASE WHEN @insCount > @delCount THEN 'inserted'
                           WHEN @insCount < @delCount THEN 'deleted' ELSE 'updated' END
IF @actionTable IN ('inserted', 'updated')
  BEGIN
    ;WITH cte AS
     (
      SELECT r.Customerid, SUM(r.Paid) AS NewTotalPaid
      FROM dbo.Receipt r  
      WHERE r.Customerid IN (SELECT i.Customerid FROM inserted i)
      GROUP BY r.Customerid
      )
      UPDATE p
      SET p.Totalpaid = c.NewTotalPaid
      FROM dbo.PaymentData p JOIN cte c ON p.Customerid = c.Customerid
  END
ELSE
  BEGIN
    ;WITH cte AS
     (
      SELECT d.Customerid, SUM(ISNULL(r.Paid, 0)) AS NewTotalPaid
      FROM deleted d LEFT JOIN dbo.Receipt r ON d.Customerid = r.Customerid
      GROUP BY d.Customerid
      )
      UPDATE p
      SET p.Totalpaid = c.NewTotalPaid
      FROM dbo.PaymentData p JOIN cte c ON p.Customerid = c.Customerid
  END