sql - 范围触发器 - 雇员薪水
问题描述
我有一张我FUNC
所有员工都在的桌子,我还有一张桌子,我可以在其中注册不同的工作角色......每个员工都必须有一个工作角色。
我的工作角色表有它的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
更新功能不会检查我的触发器内的条件并更新薪水,无论它是在我的范围内还是在我的范围之外......
解决方案
问题是inserted
包含多行。与大多数其他数据库不同,SQL Server 不支持每行 触发器(使用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
推荐阅读
- python - 在分区 Parquet 数据集上应用函数而不进行随机播放
- proxy - 父代理在 3proxy 中不起作用
- php - 更新图片链接到他们自己的 URL
- php - 如何从任何设备打开 localhost 但不使用 ngrok?
- c# - 再次指定了无效的提供程序类型
- python - 如何缩短此二维数组代码?
- javascript - 正确使用 Express 的 res.render() 和 res.redirect()
- c++ - 如何验证集合C ++中是否存在元素< x
- sql - SQL Case MAX WHEN LIKE
- amazon-ec2 - 列出标签值 - EC2 Boto3