SQL SQL触发器更新另一个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4588180/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Trigger to update another table
提问by D.R.
I have a Maximo database that has a table structure I cannot change. I am looking to copy the primary email address into the PERSON table anytime it is created or updated. The following structure describes the PERSON table and EMAIL table
我有一个 Maximo 数据库，它的表结构无法更改。我希望在创建或更新时将主要电子邮件地址复制到 PERSON 表中。下面的结构描述了 PERSON 表和 EMAIL 表
PERSON table:
人表：
PERSONID | EMAIL | ...(other irrelevant columns)...
EMAIL table:
电子邮件表：
PERSONID | EMAILADDRESS | ISPRIMARY | ...(other irrelevant columns)...
As you can see, the two tables are linked on the PERSONID column. Here is what I would like the trigger to do:
If the EMAIL table is updated or a new row is inserted, I would like to copy the EMAILADDRESS field to the corresponding entry (as linked by PERSONID) on the PERSON table IF the ISPRIMARY field is equal to 1 (1 means primary, 0 means secondary).
I have not written many triggers so I want to make sure I am only looking at the rows that are being updated or inserted into the EMAIL table and only updating the PERSON table if there is a new/updated primary email address. Thanks in advance for all of your help!
如您所见，这两个表在 PERSONID 列上链接。这是我希望触发器执行的操作：
如果更新了 EMAIL 表或插入了新行，我想将 EMAILADDRESS 字段复制到 PERSON 表上的相应条目（由 PERSONID 链接），如果是 ISPRIMARY 字段等于 1（1 表示主要，0 表示次要）。
我没有编写很多触发器，所以我想确保我只查看正在更新或插入到 EMAIL 表中的行，并且只有在有新的/更新的主电子邮件地址时才更新 PERSON 表。在此先感谢您的帮助！
UPDATE 1:
After looking at Cade's response, here is the trigger I am beginning to form:
更新 1：
查看 Cade 的回复后，这是我开始形成的触发器：
CREATE TRIGGER EMAIL_update ON UPDATE,INSERT AS BEGIN
UPDATE p
SET p.email = i.emailaddress
FROM dbo.PERSON as p
INNER JOIN inserted AS i ON i.PERSONID = p.PERSONID AND i.isprimary=1
END
I believe that trigger should work anytime something is updated OR inserted to the email table.
我相信触发器应该可以在更新或插入电子邮件表的任何时候工作。
回答by Cade Roux
Problem is going to be related to the lack of a primary key on the EMAIL table. Triggers work best with immutable primary keys.
问题将与 EMAIL 表上缺少主键有关。触发器最适合不可变的主键。
Also, what if a row changes to not be primary, do you remove the entry from PERSON?
此外，如果一行更改为非主要行，您是否从 PERSON 中删除该条目？
So still seems like some open questions in the problem domain, but here's a stab at what the trigger would look like. You could add some things which look for rows where a change is actually occurring - but be careful about NULLs.
所以在问题域中似乎仍然有一些悬而未决的问题，但这里有一个关于触发器的样子。您可以添加一些内容来查找实际发生更改的行 - 但要小心 NULL。
CREATE TRIGGER EMAIL_update ON UPDATE
AS
BEGIN
UPDATE PERSON
SET EMAIL = i.EMAILADDRESS
FROM PERSON
INNER JOIN inserted AS i
ON i.PERSONID = PERSON.PERSONID
INNER JOIN deleted AS d -- could try changing this to a left join and use same trigger for INSERT
ON -- what? could use PERSONID, but it's not unique
WHERE i.ISPRIMARY = 1 -- This helps with uniqueness, but what about things leaving primary?
-- AND i.EMAILADDRESS <> PERSON.EMAIL -- Could add this (what about NULLs?)
END
