CODEWITHSUNDEEP
sqlsql-server
ashan
ashan

Reputation: 54

Update trigger for update new table from another

I have 2 tables mpayment and account. When someone updates data in mpayment, then the trigger should automatically update the account table with the newly updated data. I wrote this trigger on my mpayment table, but when I try to update some data, I get an error:

The row value update or deleted either do make the row unique or they alter multiplerows [2 rows]

This is my trigger that I am trying to use

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[mpacupdate]
ON [dbo].[mpayment]
AFTER UPDATE 
AS
BEGIN
    DECLARE @pid AS NCHAR(10)
    DECLARE @memid AS NCHAR(10)
    DECLARE @pdate AS DATE
    DECLARE @pamount AS MONEY
    DECLARE @flag AS INT

    SELECT @pid = list.pid FROM inserted list;
    SELECT @memid = list.memid FROM inserted list;
    SELECT @pdate = list.pdate FROM inserted list;
    SELECT @pamount = list.pamount FROM inserted list;
    SELECT @flag = list.flag FROM inserted list;
BEGIN
    UPDATE [dbo].[account]
    SET memid = @memid, pdate = @pdate,
        pamount = @pamount, flag = @flag
    WHERE pid = @pid
END
END

Upvotes: 1

Views: 31

Answers (1)

Thom A
Thom A

Reputation: 95564

Your trigger is assuming that only 1 row will be updating at a time; it shouldn't. Treat the data as what it is, a dataset (not a set a scalar values).

This maynot fix the problem, as there's no sample data here to test against. I'm also not really sure that what you're after here is the right design choice, however, there's no information on what that is. Generally, you shouldn't be repeating data across tables; if you need data from another table then use a JOIN, don't INSERT or UPDATE both. If they become out of sync you'll start to get some really odd results I imagine.

Anyway, on track, this is probably what you're looking for, however, please consider the comments I've made above:

ALTER TRIGGER [dbo].[mpacupdate] ON [dbo].[mpayment]
AFTER UPDATE
AS BEGIN

    UPDATE A
    SET memid = i.memid,
        pdate = i.pdate,
        pamount = i.pamount,
        flag = i.flag
    FROM dbo.Account A
         JOIN inserted i ON A.pid = i.pid;
END

Upvotes: 1

Related Questions