SQL触发器更新另一个表 [英] SQL Trigger to update another table
问题描述
我有一个Maximo数据库,该数据库具有无法更改的表结构。我希望在创建或更新主电子邮件地址时将其复制到PERSON表中。以下结构描述了PERSON表和EMAIL表
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
PERSON表:
PERSONID | EMAIL | ...(other irrelevant columns)...
EMAIL表:
PERSONID | EMAILADDRESS | ISPRIMARY | ...(other irrelevant columns)...
如您所见,这两个表在PERSONID列上链接。这是我希望触发器执行的操作:
如果EMAIL表已更新或插入了新行,我想将EMAILADDRESS字段复制到相应的条目(按PERSONID链接)如果ISPRIMARY字段等于1,则为PERSON表(1表示主要,0表示次要)。
我没有编写很多触发器,所以我想确保只查看正在访问的行更新或插入到EMAIL表中,并且仅在有新/更新的主要电子邮件地址时更新PERSON表。预先感谢您的所有帮助!
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!
更新1:
看了Cade的回答后,这就是我要开始的触发器格式:
UPDATE 1:
After looking at Cade's response, here is the trigger I am beginning to form:
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.
推荐答案
问题将与EMAIL表上缺少主键有关。触发器与不变的主键配合使用效果最佳。
Problem is going to be related to the lack of a primary key on the EMAIL table. Triggers work best with immutable primary keys.
此外,如果某行更改为非主键,您该怎么从PERSON中删除该条目呢?
Also, what if a row changes to not be primary, do you remove the entry from PERSON?
因此在问题域中似乎仍然存在一些悬而未决的问题,但这是对触发器的外观的一种刺探。您可以添加一些查找实际发生更改的行的内容-但要注意NULL。
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.
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
这篇关于SQL触发器更新另一个表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!