SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE Schema Test

CREATE TABLE Test.Employee
(
  EmployeeNo   CHAR(8)       NOT NULL
 ,Title        VARCHAR(50)   NOT NULL
 ,FirstNm      VARCHAR(50)   NOT NULL
 ,LastNm       VARCHAR(50)   NOT NULL
 ,DateOfBirth  DATE          NOT NULL
 ,Salary       DECIMAL(9,2)  NOT NULL
 ,CONSTRAINT PK_Employee PRIMARY KEY (EmployeeNo)
 ,CONSTRAINT CK_Salary_GT_0 CHECK (Salary > 0)
 ,CONSTRAINT CK_EmployeeNo_Len_GT_0 CHECK (LEN(LTRIM(EmployeeNo)) > 0)
)

CREATE TABLE Test.EmployeeManager
(
  EmployeeNo  CHAR(8)  NOT NULL
 ,ManagerNo   CHAR(8)  NOT NULL
 ,CONSTRAINT FK_EmployeeManager_Manages_Employee FOREIGN KEY (EmployeeNo) REFERENCES Employee (EmployeeNo)
 ,CONSTRAINT FK_EmployeeManage_Is_Employee FOREIGN KEY (ManagerNo) REFERENCES Employee (EmployeeNo)
 ,CONSTRAINT PK_EmployeeManager PRIMARY KEY (EmployeeNo)
 ,CONSTRAINT CK_Employee_IsNot_Manager CHECK (EmployeeNo <> ManagerNo)
)



CREATE FUNCTION Test.Employee_SalaryIsLessThanManager
(
  @EmployeeNo  CHAR(8)
 ,@Salary      DECIMAL(9,2)
)
RETURNS BIT
AS
BEGIN
  DECLARE @Output BIT
  --Employee can't make more than manager
  IF NOT EXISTS 
    (
      SELECT 
        1 
      FROM
        EmployeeManager Mngr
      INNER JOIN
        Employee Emp
          ON Emp.EmployeeNo = Mngr.ManagerNo
      WHERE
        Mngr.EmployeeNo = @EmployeeNo
          AND Salary <= @Salary
    )
    --Subordinates can't make more than manager
    AND NOT EXISTS
    (
      SELECT 
        1 
      FROM
        EmployeeManager Mngr
      INNER JOIN
        Employee Emp
          ON Emp.EmployeeNo = Mngr.EmployeeNo
      WHERE
        Mngr.ManagerNo = @EmployeeNo
          AND Salary > @Salary
    )
    SET @Output = 1
  ELSE
    SET @Output = 0
    
  RETURN
    @Output
END
CREATE FUNCTION Test.EmployeeManager_SalaryIsGreaterThanEmployee
(
  @EmployeeNo  CHAR(8)
 ,@ManagerNo   CHAR(8)
)
RETURNS BIT
AS
BEGIN
  DECLARE @Output BIT
  IF (SELECT Salary FROM Test.Employee WHERE EmployeeNo = @EmployeeNo) < (SELECT Salary FROM Test.Employee WHERE EmployeeNo = @ManagerNo)
    SET @Output = 1
  ELSE
    SET @Output = 0
    
  RETURN
    @Output
END
ALTER TABLE Test.Employee ADD CONSTRAINT CK_Employee_Salary_IsValid CHECK (Test.Employee_SalaryIsLessThanManager(EmployeeNo,Salary)=1)
ALTER TABLE Test.EmployeeManager ADD CONSTRAINT CK_Manager_Salary_IsValid CHECK (Test.EmployeeManager_SalaryIsGreaterThanEmployee(EmployeeNo,ManagerNo)=1)
INSERT INTO Test.Employee VALUES ('B1244524','CEO','Brian','Baird','1901-01-01',1000000.00)
1 rows affected

INSERT INTO Test.Employee VALUES ('V8822349','Underling','Verace','LastName','1970-01-01',100000.00)

SELECT * FROM Test.Employee


EmployeeNo
Title
FirstNm
LastNm
DateOfBirth
Salary


B1244524
CEO
Brian
Baird
1901-01-01
1000000.00

V8822349
Underling
Verace
LastName
1970-01-01
100000.00


INSERT INTO Test.EmployeeManager VALUES ('V8822349','B1244524')  --Succeeds
1 rows affected

DELETE FROM Test.EmployeeManager WHERE EmployeeNo = 'V8822349'
1 rows affected

INSERT INTO Test.EmployeeManager VALUES ('B1244524','V8822349')  --Fails
Msg 547 Level 16 State 0 Line 1
The INSERT statement conflicted with the CHECK constraint "CK_Manager_Salary_IsValid". The conflict occurred in database "fiddle_a1f9cf5f4086425e9dd188a5fa67d822", table "Test.EmployeeManager".
Msg 3621 Level 0 State 0 Line 1
The statement has been terminated.

INSERT INTO Test.EmployeeManager VALUES ('V8822349','B1244524')
1 rows affected

UPDATE Test.Employee SET Salary = 125000.00 WHERE EmployeeNo = 'V8822349' --Succeeds
1 rows affected

UPDATE Test.Employee SET Salary = 1000001.00 WHERE EmployeeNo = 'V8822349' --Fails
Msg 547 Level 16 State 0 Line 1
The UPDATE statement conflicted with the CHECK constraint "CK_Employee_Salary_IsValid". The conflict occurred in database "fiddle_a1f9cf5f4086425e9dd188a5fa67d822", table "Test.Employee".
Msg 3621 Level 0 State 0 Line 1
The statement has been terminated.

UPDATE Test.Employee SET Salary = 1100000.00 WHERE EmployeeNo = 'B1244524' --Succeeds
1 rows affected

UPDATE Test.Employee SET Salary = 100000.00 WHERE EmployeeNo = 'B1244524' --Fails
Msg 547 Level 16 State 0 Line 1
The UPDATE statement conflicted with the CHECK constraint "CK_Employee_Salary_IsValid". The conflict occurred in database "fiddle_a1f9cf5f4086425e9dd188a5fa67d822", table "Test.Employee".
Msg 3621 Level 0 State 0 Line 1
The statement has been terminated.

SELECT * FROM Test.Employee


EmployeeNo
Title
FirstNm
LastNm
DateOfBirth
Salary


B1244524
CEO
Brian
Baird
1901-01-01
1100000.00

V8822349
Underling
Verace
LastName
1970-01-01
125000.00
EmployeeNo	Title	FirstNm	LastNm	DateOfBirth	Salary
B1244524	CEO	Brian	Baird	1901-01-01	1000000.00
V8822349	Underling	Verace	LastName	1970-01-01	100000.00