触发器中的 SQL 大小写

Question

我在SQL Server 2012
中有2个数据库我正在尝试创建一个触发器来将表从一个数据库更新到第二个数据库中的表，该表将包含任务
历史记录我正在"尝试"在更新语句中使用 CASE 表达式，但不断收到不正确的语法错误：'='
附近的语法不正确这是触发器：

USE [Employees]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[trigger_task_list]
ON [dbo].[Employee_Timesheets]
FOR INSERT, UPDATE
AS
BEGIN
SET EMPID = CASE WHEN EMPID = 2  THEN 'Carol'     
             WHEN EMPID = 3  THEN 'Joan'          
             WHEN EMPID = 4  THEN 'Jess'         
             WHEN EMPID = 5  THEN 'Linda'        
             WHEN EMPID = 6  THEN 'Tanya'     
             WHEN EMPID = 7  THEN 'Sarah'        ELSE EMPID END
SET TaskNo = CASE WHEN TaskNo = 10 THEN 'Prepare Invoices' 
              WHEN TaskNo = 20 THEN 'Log Invoices'             
              WHEN TaskNo = 30 THEN 'Print Invoices'       
              WHEN TaskNo = 40 THEN 'Run Reports' ELSE TaskNo END
SET NOCOUNT ON
INSERT INTO employeedashboard.dbo.task_list_history (emp_id,dep_id,task_id,date_time)
SELECT EMPID AS emp_id, department as dep_id, (SELECT TaskNo FROM Employee_Timesheets WHERE TaskNo = 20) AS task_id, GETDATE() AS date_time
FROM inserted 
END

解决此问题/优化查询的任何建议建议，我将不胜感激

Answer 1

我认为您要做的是这样的：

INSERT INTO employeedashboard.dbo.task_list_history (emp_id,dep_id,task_id,date_time)
SELECT 
    CASE WHEN EMPID = 2  THEN 'Carol'     
       WHEN EMPID = 3  THEN 'Joan'          
       WHEN EMPID = 4  THEN 'Jess'         
       WHEN EMPID = 5  THEN 'Linda'        
       WHEN EMPID = 6  THEN 'Tanya'     
       WHEN EMPID = 7  THEN 'Sarah'        
       ELSE EMPID 
    END AS emp_id
    , department as dep_id
    , TaskNo = 
       CASE WHEN TaskNo = 10 THEN 'Prepare Invoices' 
          WHEN TaskNo = 20 THEN 'Log Invoices'             
          WHEN TaskNo = 30 THEN 'Print Invoices'       
          WHEN TaskNo = 40 THEN 'Run Reports' 
          ELSE TaskNo 
    END AS task_id
    , GETDATE() AS date_time
FROM inserted 

请注意，这是未经测试的，纯粹基于我对我认为您要做的事情的解释......

不过，我同意戈登的观点。不要在此处对 case 语句进行编码，而是将这些值存储在随后联接到的查找表中。这将确保你将来可以轻松添加或删除值，而无需更改代码。