I have a table I called `FUNC` where all my employees are and I have another table where I am able to register different Job Roles... Each Employee must have a Job Role.

My Job Role table has its ID, the name of the role, the minimum wage of the Role and the maximum wage of the Role.

I want to create a trigger that *CHECKS* every time that I give a *RAISE* to some employee if the raise is inside the range of "Minimum Wage and Maximum Wage".

To make it easier to understand I'll show what I've done so far...

```
CREATE TABLE FUNC(
    IDFUNC INT IDENTITY,
    NAME VARCHAR(30) NOT NULL,
    SALARY MONEY NOT NULL,
    ID_JOB INT,
    IDGESTOR INT
    )
GO

CREATE TABLE JOB(
    IDJOB INT IDENTITY,
    NAMEJOB VARCHAR(10) UNIQUE,
    MINIMUM MONEY NOT NULL,
    MAXIMUM MONEY NOT NULL,
)

/* CONSTRAINTS*/

ALTER TABLE FUNC ADD CONSTRAINT PK_FUNC
PRIMARY KEY(IDFUNC)
GO

ALTER TABLE JOB ADD CONSTRAINT PK_JOB
PRIMARY KEY(IDJOB)
GO

ALTER TABLE FUNC ADD CONSTRAINT FK_GESTOR
FOREIGN KEY(IDGESTOR) REFERENCES FUNC(IDFUNC)
GO

ALTER TABLE FUNC ADD CONSTRAINT FK_FUNC_JOB
FOREIGN KEY(ID_JOB) REFERENCES JOB(IDJOB)
GO

```

Fed the Tables with those Values:

```
INSERT INTO JOB VALUES('MANAGER',5000,10000)
INSERT INTO JOB VALUES('SUPERVISOR',4000,7000)
INSERT INTO JOB VALUES('LEADER',2000,5000)
INSERT INTO JOB VALUES('ANALYST',1200,4000)
GO

IDJOB       NAMEJOB    MINIMUM               MAXIMUM
----------- ---------- --------------------- ---------------------
1           MANAGER    5000,00               10000,00
2           SUPERVISOR 4000,00               7000,00
3           LEADER     2000,00               5000,00
4           ANALYST    1200,00               4000,00

```

//

```
INSERT INTO FUNC VALUES('Name1',7000,1,NULL)
INSERT INTO FUNC VALUES('Name2',5000,2,1)
INSERT INTO FUNC VALUES('Name3',5000,2,1)
INSERT INTO FUNC VALUES('Name4',3000,3,2)
INSERT INTO FUNC VALUES('Name5',3400,3,2)
INSERT INTO FUNC VALUES('Name6',2800,3,3)
INSERT INTO FUNC VALUES('Name7',3200,3,3)
INSERT INTO FUNC VALUES('Name8',2000,4,4)
INSERT INTO FUNC VALUES('Name9',1800,4,4)
INSERT INTO FUNC VALUES('Name10',1500,4,5)
INSERT INTO FUNC VALUES('Name11',1300,4,5)
INSERT INTO FUNC VALUES('Name12',3000,4,6)
INSERT INTO FUNC VALUES('Name13',2000,4,6)
INSERT INTO FUNC VALUES('Name14',1900,4,7)
INSERT INTO FUNC VALUES('Name15',2100,4,7)
GO

IDFUNC         NAME                           SALARY               ID_JOB      IDGESTOR
------------- ------------------------------ --------------------- ----------- -----------
1             Name1                          7000,00               1           NULL
2             Name2                          5000,00               2           1
3             Name3                          5000,00               2           1
4             Name4                          3000,00               3           2
5             Name5                          3400,00               3           2
6             Name6                          2800,00               3           3
7             Name7                          3200,00               3           3
8             Name8                          2000,00               4           4
9             Name9                          1800,00               4           4
10            Name10                         1500,00               4           5
11            Name11                         1300,00               4           5
12            Name12                         3000,00               4           6
13            Name13                         2000,00               4           6
14            Name14                         1900,00               4           7
15            Name15                         2100,00               4           7

```

Finally created this trigger:

```
CREATE TRIGGER TRG_Salary
ON FUNC
FOR INSERT, UPDATE
AS
    DECLARE
    @MINIMUM MONEY, @MAXIMUM MONEY, @SALARY MONEY, @IDCARGO INT

    SELECT @IDJOB = IDJOB FROM JOB
    INNER JOIN INSERTED I
    ON IDJOB = I.ID_JOB
    WHERE IDFUNC = I.IDFUNC

    SELECT @MINIMUM = MINIMUM, @MAXIMUM = MAXIMUM
    FROM JOB WHERE IDJOB = @IDJOB

    SELECT @SALARY = I.SALARY FROM INSERTED I

    IF(@SALARY <@MINIMUM)
    BEGIN

        RAISERROR ('Salary Must be Higher than Minimum',16,1)
        ROLLBACK TRANSACTION
    END

    IF(@SALARY > @MAXIMUM)
    BEGIN

        RAISERROR ('SALARIO Must Be Lower than Maximum',16,1)
        ROLLBACK TRANSACTION
    END
GO

```

It works fine when I update just one row....

EX:

```
UPDATE FUNC SET SALARY = 15000 WHERE IDFUNC = 1
GO

```

It will show an error, because my Employee which IDFUNC=1 should receive between 5000 and 10000 SINCE HE is a Manager

My problem is when I try to update all my rows at once...

Like if I wanted to give a raise for everybody

```
UPDATE FUNC SET SALARY = SALARY*1.1

```

The update function does not check for the conditions inside my trigger and updates the salary regardless if it is inside or outside my range....