Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0301· OpenAIAi2 WildChat-4.8M (AI2)

对于Recruitment数据库,创建一个视图名为vwCandidateContractRecruiter,其中包含了外部候选人的代码,姓名,测试成绩,以及其对应的合同招聘人员的…

  1. User

    对于Recruitment数据库,创建一个视图名为vwCandidateContractRecruiter,其中包含了外部候选人的代码,姓名,测试成绩,以及其对应的合同招聘人员的代码和名称。并对该进行更新,修改外部候选人代码为‘000049’的候选人,更改其测验成绩为87分,并更改相对应的合同招聘人员的名称为‘Roger Federal’。通过创建一个触发器使得能对该视图直接通过下面的Update语句进行更新。 update vwCandidateContractRecruiter set siTestScore=87, cName=’Roger Federal’ where cCandidateCode=’000049’ 用sql server 写以上要求的sql语句

  2. 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';

  3. 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

  4. Assistant

    这个错误通常出现在 CREATE VIEW 脚本上边的语句存在错误。

    为了解决这个问题,您应该将 CREATE VIEW 语句放在单独的脚本中。您可以尝试以下两种方法:

    1. 将批处理中的其他语句移动到单独的脚本中,然后将 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;

    1. 在 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;

  5. 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.

  6. Assistant

    这个错误是由于视图 vwCandidateContractRecruiter 查询中涉及的基本表 ExternalCandidate 和 ContractRecruiter 都收到了更新,并且该视图引用了基本表的多个列。这种情况下,视图不允许直接更新,因为它无法确定应该是如何处理这些复杂的更新操作的。

    您可以尝试以下两种方法解决这个问题:

    1. 分别编写两个触发器,一个用于更新 ExternalCandidate 的数据,另一个用于更新 ContractRecruiter 数据。这种方法需要您在视图中使用 JOIN 语句来访问多个基本表。

    2. 对于视图中涉及多个表的查询,尝试使用 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 触发器进行更新。

  7. 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视图并没有更新?哪里出现了错误

  8. Assistant

    视图更新不成功的原因可能有很多,以下是一些可能出现问题的地方以及可能的解决方案:

    1. 触发器没有正确工作

    您可以尝试通过单独执行触发器中的 UPDATE 语句来检查是否可以正确更新数据。如果您发现更新基本表的操作失败,则需要检查您的 UPDATE 语句是否正确。

    1. 数据类型不匹配

    检查 siTestScore 列和 Varchar 列的数据类型是否匹配。如果这两列的数据类型不匹配,则会导致更新操作失败。

    1. 基本表中的数据不匹配

    检查更新语句中的 cCandidateCode 是否正确匹配基本表中的数据。如果数据不匹配,则更新操作将失败。

    1. 视图和基本表之间的关系不正确

    如果 vwCandidateContractRecruiter 视图和外部候选人表 ExternalCandidate、合同招聘人员表 ContractRecruiter 之间的关系不正确,则可能会导致更新操作失败。请检查视图定义以确保它正确匹配基本表的结构。

    一旦您检查并确定了这些问题并解决了它们,您应该能够更新视图中的数据。

  9. 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'

  10. 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 操作之前已正确插入了相应的数据。正确的数据插入将确保您能够正确地更新视图中的数据。