对于Recruitment数据库,创建一个视图名为vwCandidateContractRecruiter,其中包含了外部候选人的代码,姓名,测试成绩,以及其对应的合同招聘人员的…
- User
对于Recruitment数据库,创建一个视图名为vwCandidateContractRecruiter,其中包含了外部候选人的代码,姓名,测试成绩,以及其对应的合同招聘人员的代码和名称。并对该进行更新,修改外部候选人代码为‘000049’的候选人,更改其测验成绩为87分,并更改相对应的合同招聘人员的名称为‘Roger Federal’。通过创建一个触发器使得能对该视图直接通过下面的Update语句进行更新。 update vwCandidateContractRecruiter set siTestScore=87, cName=’Roger Federal’ where cCandidateCode=’000049’ 用sql server 写以上要求的sql语句
- Assistant
创建视图:
CREATE VIEW vwCandidateContractRecruiter AS SELECT c.cCandidateCode, c.cName AS cName, c.siTestScore, r.cRecruiterCode, r.cName AS rName FROM tblCandidate c JOIN tblContractRecruiter r ON c.cRecruiterCode=r.cRecruiterCode WHERE c.cCandidateType='External';
创建触发器:
CREATE TRIGGER trgUpdateVwCandidateContractRecruiter ON vwCandidateContractRecruiter INSTEAD OF UPDATE AS BEGIN UPDATE c SET c.siTestScore=i.siTestScore, r.cName=i.cName FROM tblCandidate c JOIN tblContractRecruiter r ON c.cRecruiterCode=r.cRecruiterCode JOIN inserted i ON c.cCandidateCode=i.cCandidateCode; END;
执行更新:
update vwCandidateContractRecruiter set siTestScore=87, cName='Roger Federal' where cCandidateCode='000049';
- User
use Recruitment CREATE VIEW vwCandidateContractRecruiter AS SELECT ec.cCandidateCode, ec.vFirstName, ec.vLastName, ec.siTestScore, ec.cContractRecruiterCode,cr.cName FROM ExternalCandidate ec left JOIN ContractRecruiter cr ON ec.cContractRecruiterCode = cr.cContractRecruiterCode systax error create view must be the only statement in the batch
- Assistant
这个错误通常出现在 CREATE VIEW 脚本上边的语句存在错误。
为了解决这个问题,您应该将 CREATE VIEW 语句放在单独的脚本中。您可以尝试以下两种方法:
- 将批处理中的其他语句移动到单独的脚本中,然后将 CREATE VIEW 放在一个单独的脚本中:
-- 批处理脚本中包含其他语句的示例 USE Recruitment; GO
-- CREATE VIEW 语句放在单独的脚本中 CREATE VIEW vwCandidateContractRecruiter AS SELECT ec.cCandidateCode, ec.vFirstName, ec.vLastName, ec.siTestScore, ec.cContractRecruiterCode,cr.cName FROM ExternalCandidate ec left JOIN ContractRecruiter cr ON ec.cContractRecruiterCode = cr.cContractRecruiterCode;
- 在 CREATE VIEW 脚本之前添加一条 GO 语句以将其分开:
USE Recruitment; GO
-- 加上 GO 语句将 CREATE VIEW 和其他语句分开 GO
CREATE VIEW vwCandidateContractRecruiter AS SELECT ec.cCandidateCode, ec.vFirstName, ec.vLastName, ec.siTestScore, ec.cContractRecruiterCode,cr.cName FROM ExternalCandidate ec left JOIN ContractRecruiter cr ON ec.cContractRecruiterCode = cr.cContractRecruiterCode;
- User
CREATE TRIGGER tr_vwCandidateContractRecruiter ON vwCandidateContractRecruiter INSTEAD OF UPDATE AS BEGIN UPDATE vwCandidateContractRecruiter SET siTestScore =i.siTestScore, cName=i.cName FROM ExternalCandidate ec JOIN ContractRecruiter cr ON cr.cContractRecruiterCode=ec.cContractRecruiterCode JOIN inserted i ON ec.cCandidateCode=i.cCandidateCode; END; View or function 'vwCandidateContractRecruiter' is not updatable because the modification affects multiple base tables.
- Assistant
这个错误是由于视图
vwCandidateContractRecruiter查询中涉及的基本表ExternalCandidate和ContractRecruiter都收到了更新,并且该视图引用了基本表的多个列。这种情况下,视图不允许直接更新,因为它无法确定应该是如何处理这些复杂的更新操作的。您可以尝试以下两种方法解决这个问题:
-
分别编写两个触发器,一个用于更新
ExternalCandidate的数据,另一个用于更新ContractRecruiter数据。这种方法需要您在视图中使用 JOIN 语句来访问多个基本表。 -
对于视图中涉及多个表的查询,尝试使用 INSTEAD OF 触发器将所有必要的更新操作合并到一个操作中,并通过编写复杂的 UPDATE 子句来执行这些更新。
在这里,以下是您可以尝试的第二种方法的示例代码:
CREATE TRIGGER tr_vwCandidateContractRecruiter ON vwCandidateContractRecruiter INSTEAD OF UPDATE AS BEGIN UPDATE ec SET ec.siTestScore = i.siTestScore FROM ExternalCandidate ec INNER JOIN inserted i ON ec.cCandidateCode = i.cCandidateCode UPDATE cr SET cr.cName = i.cName FROM ContractRecruiter cr INNER JOIN ExternalCandidate ec ON cr.cContractRecruiterCode = ec.cContractRecruiterCode INNER JOIN inserted i ON ec.cCandidateCode = i.cCandidateCode END这个触发器将更新
ExternalCandidate和ContractRecruiter表中的数据以保持视图与基本表的数据一致。注意,视图必须具有唯一行标识符才能使用 INSTEAD OF 触发器进行更新。 -
- User
use Recruitment go CREATE VIEW vwCandidateContractRecruiter AS SELECT ec.cCandidateCode, CONCAT(ec.vFirstName,' ',ec.vLastName) as cName, ec.siTestScore,ec.cContractRecruiterCode,cr.cName as cContractRecruiterName FROM ExternalCandidate ec left JOIN ContractRecruiter cr ON ec.cContractRecruiterCode = cr.cContractRecruiterCode go --drop view vwCandidateContractRecruiter
drop trigger if exists tr_vwCandidateContractRecruiter go CREATE TRIGGER tr_vwCandidateContractRecruiter ON vwCandidateContractRecruiter INSTEAD OF UPDATE AS BEGIN UPDATE ec SET ec.siTestScore = i.siTestScore FROM ExternalCandidate ec INNER JOIN inserted i ON ec.cCandidateCode = i.cCandidateCode
UPDATE cr SET cr.cName = i.cName FROM ContractRecruiter cr INNER JOIN ExternalCandidate ec ON cr.cContractRecruiterCode = ec.cContractRecruiterCode INNER JOIN inserted i ON ec.cCandidateCode = i.cCandidateCode END
update vwCandidateContractRecruiter set siTestScore=87, cName='Roger Federal' where cCandidateCode='000049' 执行update vwCandidateContractRecruiter 语句后,vwCandidateContractRecruiter视图并没有更新?哪里出现了错误
- Assistant
视图更新不成功的原因可能有很多,以下是一些可能出现问题的地方以及可能的解决方案:
- 触发器没有正确工作
您可以尝试通过单独执行触发器中的 UPDATE 语句来检查是否可以正确更新数据。如果您发现更新基本表的操作失败,则需要检查您的 UPDATE 语句是否正确。
- 数据类型不匹配
检查
siTestScore列和Varchar列的数据类型是否匹配。如果这两列的数据类型不匹配,则会导致更新操作失败。- 基本表中的数据不匹配
检查更新语句中的
cCandidateCode是否正确匹配基本表中的数据。如果数据不匹配,则更新操作将失败。- 视图和基本表之间的关系不正确
如果
vwCandidateContractRecruiter视图和外部候选人表ExternalCandidate、合同招聘人员表ContractRecruiter之间的关系不正确,则可能会导致更新操作失败。请检查视图定义以确保它正确匹配基本表的结构。一旦您检查并确定了这些问题并解决了它们,您应该能够更新视图中的数据。
- User
对于Recruitment数据库,创建一个视图名为vwCandidateContractRecruiter,其中包含了外部候选人的代码,姓名,测试成绩,以及其对应的合同招聘人员的代码和名称。并对该进行更新,修改外部候选人代码为‘000049’的候选人,更改其测验成绩为87分,并更改相对应的合同招聘人员的名称为‘Roger Federal’。通过创建一个触发器使得能对该视图直接通过下面的Update语句进行更新。 update vwCandidateContractRecruiter set siTestScore=87, cName=’Roger Federal’ where cCandidateCode=’000049’
use Recruitment go CREATE VIEW vwCandidateContractRecruiter AS SELECT ec.cCandidateCode, CONCAT(ec.vFirstName,' ',ec.vLastName) as cName, ec.siTestScore,ec.cContractRecruiterCode,cr.cName as cContractRecruiterName FROM ExternalCandidate ec left JOIN ContractRecruiter cr ON ec.cContractRecruiterCode = cr.cContractRecruiterCode go --drop view vwCandidateContractRecruiter drop trigger if exists tr_vwCandidateContractRecruiter go CREATE TRIGGER tr_vwCandidateContractRecruiter ON vwCandidateContractRecruiter INSTEAD OF UPDATE AS BEGIN UPDATE ec SET ec.siTestScore = i.siTestScore FROM ExternalCandidate ec INNER JOIN inserted i ON ec.cCandidateCode = i.cCandidateCode UPDATE cr SET cr.cName = i.cName FROM ContractRecruiter cr INNER JOIN ExternalCandidate ec ON cr.cContractRecruiterCode = ec.cContractRecruiterCode INNER JOIN inserted i ON ec.cCandidateCode = i.cCandidateCode END
update vwCandidateContractRecruiter set siTestScore=87, cName='Roger Federal' where cCandidateCode='000049'
- Assistant
根据您提供的 SQL,视图以及触发器都已经创建。以下是可以更新视图
vwCandidateContractRecruiter中外部候选人代码为000049的记录,并将其测验成绩更新为 87 分,合同招聘人员名称更改为Roger Federal的 SQL 代码:UPDATE vwCandidateContractRecruiter SET siTestScore = 87, cContractRecruiterName = 'Roger Federal' WHERE cCandidateCode = '000049';请注意,视图
vwCandidateContractRecruiter中的cName列是通过使用CONCAT函数从外部候选人的vFirstName列和vLastName列组合而成的,因此此列将不受此 UPDATE 语句的影响。此外,应该确保在执行任何 UPDATE 操作之前已正确插入了相应的数据。正确的数据插入将确保您能够正确地更新视图中的数据。