使用触发器显示付款总额+奖金并显示为费用 [英] Working With Triggers to Display Sum of Pays + Bonus and display as Expenses

问题描述

尝试使用触发器来总结2列住房（付款和奖金）并将结果显示为费用。 

我有一个有10行的表成员。 

每个人都有'付款'栏和'奖金'栏。 

我正在尝试编写一个名为每当一行运行或更新时的费用，它将显示标题费用下的工资+奖金总额。 

我写了这个，但给了我错误，试图看看我是否我正确地做了。 

我错过了什么+做错了。 

创造触发费用$
ON table1

INSERT INSERT，UPDATE

AS

BEGIN

DECLARE @Pay int，@ Bonus int < br $>
SELECT @Pay = Pay，@ Bonus = Bonus

UPDATE员工设置总费用= @Salary + @Comm

结束¥ b $ b GO

解决方案

你是假设触发器总是有一行要显示可能并非总是如此

您需要的是下面的内容

创建触发费用
 ON table1 
 AFTER INSERT，UPDATE 
 AS 
 BEGIN 
 
 UPDATE e 
 set [总费用] = i.Pay + i.Bonus 
 FROM Employee e 
 JOIN INSERTED i 
 ON i.PK = e.PK 
 END 
 GO 

PK是表的主键列

INSERTED表只包含当前更新/插入的行

实际上，对于上述要求，您只需要在表格上的计算列而不是触发器

，即只需添加一列如

 ALTER TABLE员工ADD [总费用] AS Pay + Bonus 

并且列将在运行时基于运行时自动计算如果Bonus列可以为空，则为Pay和Bonus列值

使其像

 ALTER TABLE Employee ADD [Total费用] AS Pay + COALESCE（红利，0）

Trying to work with triggers to sum up 2 columns housing (Pay and Bonus) and display the result as Expenses. 

I have a table that has 10 rows of members. 

Each have 'Pay' column and 'Bonus' column. 

I'm trying to write a trigger called expenses for whenever a row is run or updated it'll display the sum of pay + bonus under a title expenses. 

I wrote this but gives me errors and trying to see if I'm doing it correctly. 

What am I missing + doing wrong. 

CREATE TRIGGER Expenses
ON table1
AFTER INSERT, UPDATE
AS
BEGIN
DECLARE @Pay int, @Bonus int
SELECT @Pay = Pay , @Bonus = Bonus
UPDATE Employee set Total Expenses = @Salary + @Comm
END
GO

解决方案

you're assuming trigger will always have one row to display which may not be the case always

What you would need is something like below

CREATE TRIGGER Expenses
ON table1
AFTER INSERT, UPDATE
AS
BEGIN

UPDATE e 
set [Total Expenses] = i.Pay + i.Bonus
FROM Employee e
JOIN INSERTED i
ON i.PK = e.PK
END
GO

PK is your primary key column of the table

INSERTED table will only include currently updated/inserted rows

Actually for the above requirement you would just require a calculated column on the table rather than a trigger

i.e just add a column like

ALTER TABLE Employee ADD [Total Expenses] AS Pay + Bonus

And column will get autocalculated at runtime based on Pay and Bonus column values

if Bonus column is nullable make it like

ALTER TABLE Employee ADD [Total Expenses] AS Pay + COALESCE(Bonus,0)

这篇关于使用触发器显示付款总额+奖金并显示为费用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！