CODEWITHSUNDEEP
sqlsql-server
GROVER_SYAAN
GROVER_SYAAN

Reputation: 365

sql server trigger pulling data from joined table

i want to create a trigger for insert update and delete on a table that is linked with other tables when any of the above happens the trigger should pull one values from the inserted table and other values from the joined tables and insert those into another table

here is a simplified answer, but i am getting an error on the employee join saying The multi-part identifier could not be bound

Create TRIGGER triggertest
ON salary 
FOR INSERT
AS 
BEGIN


DECLARE @salary int
DECLARE @firstName int


SELECT @salary = i.salary, @firstName =employee.firstName
FROM inserted i 


        INNER JOIN employee  ON
             i.employeeID = emplopyee.employeeID 
        INNER JOIN deleted d ON  i.salaryID = d.salaryID 





    INSERT INTO testTAble
    (employeeFirstName,EmployeeSalary)
    select @firstName ,@salary 

END

Upvotes: 1

Views: 3422

Answers (2)

Joe Taras
Joe Taras

Reputation: 15389

Your request can be:

INSERT

CREATE TRIGGER triggertestI
ON salary 
FOR INSERT
AS 
BEGIN
    INSERT INTO testTAble
    (employeeFirstName,EmployeeSalary)
    SELECT i.salary, employee.firstName
    FROM inserted i 
    INNER JOIN employee
    ON i.employeeID = employee.employeeID 
END

UPDATE

CREATE TRIGGER triggertestU
ON salary 
FOR UPDATE
AS 
BEGIN
    UPDATE testTAble
    SET EmployeeSalary = 
    (SELECT i.salary
    FROM inserted i 
    INNER JOIN employee
    ON i.employeeID = employee.employeeID
    where i.employeeID = testTable.employeeID)
END

DELETE

CREATE TRIGGER triggertestD
ON salary 
FOR DELETE
AS 
BEGIN
    DELETE FROM testTAble
    WHERE employeeID in (
    SELECT i.employeeID
    FROM deleted i 
    INNER JOIN employee
    ON i.employeeID = employee.employeeID
    where i.employeeID = testTable.employeeID)
END

P.S. I think, in your query the error is: emplopyee (you have add acidentally un p in the table name employee)

Upvotes: 1

Suraj Shrestha
Suraj Shrestha

Reputation: 1808

error here:

i.employeeID = emplopyee.employeeID

extra 'p' is added in your emplopyee.employeeID. emplopyee must be employee

Upvotes: 0

Related Questions