CODEWITHSUNDEEP
sql-servert-sqldatabase-trigger
user7136568
user7136568

Reputation:

Writing a SQL Server trigger - error

I have three tables:

The question is to write a trigger. It states that if a bus driver's salary is increased by 20% more than the original salary then 0.05% of the increased value will be transferred to his Station as Salary commission.

I managed to write the trigger halfway, but got stuck when I have to transfer the amount to the other table.

My code is:

CREATE TRIGGER tr_1 
ON Bus_Driver
AFTER INSERT
AS
BEGIN
    DECLARE @salary MONEY
    SET @Salary = 0

    SELECT @Salary= salary 
    FROM Inserted
    WHERE @Salary > (120 / 100 * @Salary)

Can anyone help me how to write the next steps please

Upvotes: 1

Views: 88

Answers (2)

Zohar Peled
Zohar Peled

Reputation: 82474

The trigger you wrote is wrong.

First, it's a trigger for insert, while the question states that the salary is raised, meaning it should be a trigger for update.

Second, your trigger assumes only a single row will be in the inserted table. However, this assumption is wrong. Triggers in SQL server are fired per statement, not per row, meaning that the inserted (and deleted) tables might contain zero, one, or many rows.

A solution for this question will be to write a trigger for update, that will, in turn, update the station table. Something like this:

CREATE TRIGGER tr_Bus_Driver_Update ON Bus_Driver
FOR UPDATE
AS

    UPDATE s
    SET Salary_Commission = Salary_Commission - 
                            (0.0005 * D.Salary) + -- Remove old salary of the driver(s) from salary_commition. 
                            (0.0005 * I.Salary) -- add new salary of the driver(s) to salary_commition
    FROM Station s
    INNER JOIN Inserted I ON s.StationNo = I.StationNo
    INNER JOIN Deleted D ON I.drNo = D.drNo -- assuming drNo is unique in Bus_Driver table
    WHERE I.Salary >= D.Salady * 1.2 -- You might need to cast to a floating point data type if the Salary is an integer data type

Note you might need to cast the salary to a floating point data type if the Salary is an integer data type whenever it's used in this trigger.

Upvotes: 1

marc_s
marc_s

Reputation: 754508

You need to stop and start again from scratch.

First of all, you need to catch the AFTER UPDATE event - not the insert - since you want to do something when the salary is updated (an existing value is replaced with a higher one).

Secondly, the trigger will be called once per UPDATE statement and if that UPDATE affects more than one row, the Deleted and Inserted pseudo tables will contain multiple rows of data - so your SELECT @Salary = salary FROM Inserted statement is doomed - it will fetch one arbitrary row and ignore all others that might be also affected.

In an UPDATE case, Inserted will have the new values (after the update), while Deleted has the old values (before the update) - so the difference between these two pseudo tables can be used to figure out if the salary increase was more than 20%:

CREATE TRIGGER trBusDriverSalaryIncrease
ON dbo.Bus_Driver
AFTER UPDATE
AS
BEGIN
    -- declare a table variable to hold all revelant values
    DECLARE @RelevantIncreases TABLE (drNo INT, StationNo INT, SalaryIncrease DECIMAL(18,2))

    -- find those bus drivers who have had a more than 20% increase in their salary
    INSERT INTO @relevantIncreases (drNo, StationNo, SalaryIncrease)
        SELECT
            i.drNo, i.StationNo,    -- Driver and Station No
            (i.Salary - d.Salary)   -- Salary increase in absolute numbers
        FROM
            Deleted d
        INNER JOIN
            Inserted i ON d.drNo = i.drNo
        WHERE
            -- Salary increased by more than 20% 
            i.Salary > 1.2 * d.Salary

    -- now that we have all the relevant bus drivers and their salary increase
    -- insert this into the Station.Salary_Commission column
    UPDATE s
    SET Salary_Commission = s.Salary_Commission + ri.SalaryIncrease * 0.0005
    FROM dbo.Station s
    INNER JOIN @RelevantIncreases ri ON ri.StationNo = s.StationNo  
END

Upvotes: 0

Related Questions