CODEWITHSUNDEEP
sqlsql-servert-sql
MitkoZ
MitkoZ

Reputation: 49

SQL Trigger that should update other table

I am trying to make a trigger that will update a table only if something is inserted in an other table 

CREATE TABLE Purchases
(
PurchaseID INT IDENTITY NOT NULL PRIMARY KEY,
DateBought SMALLDATETIME,
Price MONEY CHECK (Price>0),
RealEstateID INT UNIQUE NOT NULL,
DealMadeByEmployeeID INT NOT NULL
);


INSERT INTO Purchases(DateBought, Price, RealEstateID, DealMadeByEmployeeID)
VALUES ('20161015 15:10:03','120000',3, 3); --Апартамент 170 квадратни метра

INSERT INTO Purchases(DateBought, Price, RealEstateID, DealMadeByEmployeeID)
VALUES ('20161219 13:13:30','200000',4, 3); --Къща 500 квадратни метра

INSERT INTO Purchases (Price,RealEstateID,DealMadeByEmployeeID)
VALUES ('130000',6,3); --непродаден апартамент

CREATE TABLE EmployeesSalary
(
EmployeeID INT NOT NULL PRIMARY KEY,
CurrentSalary MONEY DEFAULT 0,-- на процент
MonthlySalesMade INT DEFAULT 0,
MonthlyRentsMade INT DEFAULT 0
);

INSERT INTO EmployeesSalary (EmployeeID, CurrentSalary)
VALUES (1, '400');

INSERT INTO EmployeesSalary (EmployeeID, CurrentSalary)
VALUES (2, '400');

INSERT INTO EmployeesSalary (EmployeeID, CurrentSalary)
VALUES (3, '400');

CREATE TRIGGER tr_EmployeesSalaryPurchasesUpdate --при INSERT в Purchases таблицата
ON Purchases
AFTER INSERT
AS
BEGIN
    UPDATE EmployeesSalary
    SET EmployeesSalary.MonthlySalesMade=EmployeesSalary.MonthlySalesMade+1
    WHERE EmployeesSalary.EmployeeID IN (SELECT inserted.DealMadeByEmployeeID FROM inserted)
END

but based on the IDs. My code doesn't get any errors but the trigger doesn't seem to work. What is my mistake?

https://postimg.org/image/rot0xcriv/

Upvotes: 0

Views: 73

Answers (3)

cloudsafe
cloudsafe

Reputation: 2504

It won't work if there are multiple updates unless NOCOUNT is SET to ON:

CREATE TRIGGER tr_EmployeesSalaryPurchasesUpdate 
ON Purchases
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON -- new line of code
    UPDATE EmployeesSalary
    SET EmployeesSalary.MonthlySalesMade=EmployeesSalary.MonthlySalesMade+1
    WHERE EmployeesSalary.EmployeeID IN (SELECT inserted.DealMadeByEmployeeID FROM inserted)
END

Upvotes: 1

M.Ali
M.Ali

Reputation: 69494

  1. You can also use the EXISTS operator.
  2. Also beaware of the possible NULLS in MonthlySalesMade column, +1 update will not work if there is a null values in that column. 
CREATE TRIGGER tr_EmployeesSalaryPurchasesUpdate 
ON Purchases
AFTER INSERT
AS
BEGIN
    UPDATE EmployeesSalary
    SET MonthlySalesMade = ISNULL(MonthlySalesMade, 0)+1
    WHERE EXISTS (SELECT 1 
                  FROM inserted
                  WHERE EmployeesSalary.EmployeeID = inserted.DealMadeByEmployeeID )
END

Upvotes: 0

Lamak
Lamak

Reputation: 70638

You should use an INNER JOIN there:

CREATE TRIGGER tr_EmployeesSalaryPurchasesUpdate --при INSERT в Purchases таблицата
ON Purchases
AFTER INSERT
AS
BEGIN
    UPDATE ES 
    SET ES.MonthlySalesMade = ES.MonthlySalesMade+1
    FROM EmployeesSalary ES
    INNER JOIN INSERTED I
        ON ES.EmployeeID = I.DealMadeByEmployeeID;
END

Upvotes: 2

Related Questions