触发更新列的总和？(Trigger to update sum of columns?)

我知道这不是一种方法，但这是一个面试问题

使用触发器更新total = marks1 + marks2 + marks3。

我写了类似的东西，但是在插入语句后它没有更新。

CREATE table marks ( marks1 int, marks2 int, marks3 int, total int ) SELECT * from marks m insert into marks values(10,10,20,0) drop TRIGGER total_marks create TRIGGER total_marks ON marks AFTER INSERT AS begin SET NOCOUNT ON DECLARE @marks1 as int select @marks1 = inserted.marks1 FROM inserted DECLARE @marks2 as int select @marks1 = inserted.marks2 FROM inserted DECLARE @marks3 as int select @marks1 = inserted.marks3 FROM inserted DECLARE @result as int set @result = @marks1 + @marks2 + @marks3 update marks set total = @result SET NOCOUNT OFF end

I know this is not a way to do it but it's a interview question

to update total = marks1 + marks2 + marks3 using a trigger.

I wrote something like this but it's not updating after an insert statement.

CREATE table marks ( marks1 int, marks2 int, marks3 int, total int ) SELECT * from marks m insert into marks values(10,10,20,0) drop TRIGGER total_marks create TRIGGER total_marks ON marks AFTER INSERT AS begin SET NOCOUNT ON DECLARE @marks1 as int select @marks1 = inserted.marks1 FROM inserted DECLARE @marks2 as int select @marks1 = inserted.marks2 FROM inserted DECLARE @marks3 as int select @marks1 = inserted.marks3 FROM inserted DECLARE @result as int set @result = @marks1 + @marks2 + @marks3 update marks set total = @result SET NOCOUNT OFF end

最满意答案

您的触发器不处理多行插入，将所有行更新为相同的值（而不仅仅是插入的行），并且远比必要复杂得多。你的钥匙在哪里？

CREATE TRIGGER dbo.total_marks ON dbo.marks FOR INSERT AS BEGIN SET NOCOUNT ON; UPDATE m SET total = i.marks1 + i.marks2 + i.marks3 FROM dbo.marks AS m INNER JOIN inserted AS i ON m.key = i.key; END GO

如果你的表真的没有密钥（它对我来说没有多大意义），那么你可以这样说，但它可能会更新已更新的行：

ON m.marks1 = i.marks1 AND m.marks2 = i.marks2 AND m.marks3 = i.marks3 WHERE m.total = 0;

Your trigger doesn't handle multiple row inserts, updates all rows to the same value (rather than just the row(s) inserted), and is far more complex than necessary anyway. Where is your key?

CREATE TRIGGER dbo.total_marks ON dbo.marks FOR INSERT AS BEGIN SET NOCOUNT ON; UPDATE m SET total = i.marks1 + i.marks2 + i.marks3 FROM dbo.marks AS m INNER JOIN inserted AS i ON m.key = i.key; END GO

If your table really doesn't have a key (it doesn't make a whole lot of sense to me), then you can say this, but it may update rows that were already updated:

ON m.marks1 = i.marks1 AND m.marks2 = i.marks2 AND m.marks3 = i.marks3 WHERE m.total = 0;

更多推荐