CODEWITHSUNDEEP
sql-servertriggers
StackUser
StackUser

Reputation: 1

Trigger updates all value from table

I've written a trigger that must update CCCMPMTRL table when the column PRICER01 from MTRL table updates. The primary key from the table MTRL is also a column named MTRL which I also keep in the table that must update (CCCMPMTRL) as a foreign key. The thing is that whenever I update the MTRL table, in the CCCMPMTRL, values update for all the records I have in MTRL, but I want for one only, the current one. What do I do wrong?

Code looks like this:

CREATE TRIGGER dbo.[test_Price]
ON dbo.[MTRL]
AFTER UPDATE
AS
    DECLARE @MTRL INT;
    DECLARE @OLDPRICER01 FLOAT(2);
    DECLARE @NEWPRICER01 FLOAT(2);
    DECLARE @MPMTRL INT;
    SET @MTRL = (SELECT I.MTRL FROM INSERTED I,MTRL M WHERE M.MTRL = I.MTRL);
    SET @MPMTRL = (SELECT MTRL FROM MTRL WHERE MTRL=@MTRL);
    SET @OLDPRICER01 = (SELECT PRICER01 FROM MTRL WHERE MTRL =@MTRL AND SODTYPE = 51)
    SET @NEWPRICER01 = (SELECT PRICER01 FROM INSERTED WHERE MTRL = @MTRL AND SODTYPE = 51)
    IF(NOT EXISTS(SELECT CM.MTRL FROM CCCMPMTRL CM, INSERTED I WHERE CM.MTRL = I.MTRL))
        INSERT INTO CCCMPMTRL
            SELECT M.MTRL,D.PRICER01,I.PRICER01 FROM INSERTED I, DELETED D, MTRL M WHERE I.MTRL = @MTRL
    ELSE
        IF(@OLDPRICER01 <> @NEWPRICER01)
            UPDATE CCCMPMTRL 
            SET OLDPRICER01 = @OLDPRICER01,
                NEWPRICER01 = @NEWPRICER01
                    FROM CCCMPMTRL AS CM 
                    WHERE CM.MTRL = @MTRL

Upvotes: 0

Views: 53

Answers (1)

mikeagg
mikeagg

Reputation: 751

Try this

UPDATE CM
    SET OLDPRICER01 = D.PRICER01,
        NEWPRICER01 = I.PRICER01
    FROM INSERTED I
        JOIN DELETED D ON D.MTRL=I.MTRL
        JOIN CCCMPMTRL CM ON CM.MTRL=I.MTRL
    WHERE D.PRICER01 <> I.PRICER01

INSERT INTO CCCMPMTRL
    SELECT I.MTRL, D.PRICER01, I.PRICER01 
    FROM INSERTED I
        JOIN DELETED D ON D.MTRL=I.MTRL
        LEFT JOIN CCCMPMTRL C ON C.MTRL=I.MTRL
    WHERE C.MTRL IS NULL

Because the UPDATE statement uses an inner join on CCMPMTRL it will only update where a row already exists, whereas the INSERT statement uses a left outer join to exclude existing values of MTRL.

This should work whether you are inserting one or many rows at once, and there is no need for all those local variables.

Upvotes: 2

Related Questions