范围触发器-员工工资

wxclj1h5 于 2021-07-26 发布在 Java

关注(0)|答案(3)|浏览(356)

我有一张我叫过的table FUNC 我所有的员工都在那里，我还有一张table，在那里我可以注册不同的工作角色。。。每个员工必须有一个工作角色。
我的工作角色表有其id、角色名称、角色的最低工资和角色的最高工资。
我想创建一个触发器，每当我给某个员工加薪时检查加薪是否在“最低工资和最高工资”的范围内。
为了更容易理解，我将展示我到目前为止所做的。。。

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

用这些值填充表格：

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

最终创建了此触发器：

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

当我只更新一行时，它工作正常。。。。
前任：

UPDATE FUNC SET SALARY = 15000 WHERE IDFUNC = 1
GO

它将显示一个错误，因为我的员工idfunc=1应该收到5000到10000之间的工资，因为他是一个经理
我的问题是当我试图一次更新所有行时。。。
就像我想给每个人加薪一样

UPDATE FUNC SET SALARY = SALARY*1.1

更新功能不会检查触发器内的条件，并更新薪资，无论它是在我的范围内还是在我的范围外。。。。

sql sql-server database-trigger sql-update sql-insert

来源：https://stackoverflow.com/questions/62182799/range-triggers-salary-of-employess

3条答案

按热度按时间

envsm3lx1#

问题是 inserted 包含多行。与大多数其他数据库不同，SQLServer不支持每行触发器（使用 for each row 条款）。
您需要一次检查所有插入的行，如果有任何行未通过检查，则将其全部拒绝。下面是一种使用条件聚合的方法：

create trigger trg_salary
on func
for insert, update
as
    declare @isfailed int

    select @isfailed = max(
        case when i.salary not between j.minimum and j.maximum 
            then 1 
            else 0 
        end
    )
    from inserted i
    inner join job j on j.idjob = i.idjob

    if(@isfailed = 1)
    begin
        raiserror ('salary must be belong to the minimum-maximum range',16,1)
        rollback transaction
    end

go

赞(0）回复(0）举报 2021-07-26

n1bvdmb62#

这个 INSERTED 伪表包含0-n行，其中n是插入/更新的行数（可以是零！）。正因为如此，而且sql是为“基于集的操作”而优化的，所以您需要构建一个查询来测试错误条件。
但是，正因为如此，除非要返回错误列表，否则即使存在多个错误，也只能返回1个错误。
它的最佳实践使用 THROW 而不是 RAISERROR .

CREATE TRIGGER TRG_Salary ON FUNC
FOR INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @ERROR NVARCHAR(2048);

    SELECT TOP 1 @ERROR = CASE WHEN I.SALARY > MAXIMUM THEN 'SALARIO Must Be Lower than Maximum.' WHEN I.SALARY < MAXIMUM THEN 'SALARIO Must Be Higher than Minimum.' END
    FROM INSERTED I
    INNER JOIN JOB J on J.IDJOB = I.ID_JOB
    WHERE I.SALARY > MAXIMUM OR I.SALARY < MAXIMUM;

    IF @ERROR IS NOT NULL BEGIN
        ROLLBACK;
        THROW 51000, @ERROR, 1;  
    END;
END
GO

赞(0）回复(0）举报 2021-07-26

lg40wkob3#

sql小提琴
ms sql server 2017架构设置：

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

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
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

CREATE TRIGGER TRG_Salary
ON FUNC
FOR INSERT, UPDATE
AS

    IF EXISTS
    (
        SELECT 1
        FROM JOB J
        INNER JOIN INSERTED F
        ON J.IDJOB = F.ID_JOB
        WHERE F.Salary < J.Minimum OR F.Salary > J.Minimum
    )    
    BEGIN
        RAISERROR ('SALARIO Must Be Lower than Maximum and Greater than Minimum',16,1)
        ROLLBACK TRANSACTION
    END
GO

查询1：

--UPDATE FUNC SET SALARY = 15000 WHERE IDFUNC = 1

UPDATE FUNC SET SALARY = SALARY*1.1

结果：salario必须低于最大值且大于最小值

赞(0）回复(0）举报 2021-07-26

我来回答

范围触发器-员工工资

3条答案

相关问题

热门标签

最新问答