在 Insert 语句的值部分使用 Case 语句 [英] Using a Case statement within the values section of an Insert statement
问题描述
请原谅我的无知和糟糕的 SQL 编程技能,但我通常是一个基本的 SQL 开发人员.
Please forgive my ignorance and poor SQL programming skills but I am normally a basic SQL developer.
我需要通过在一个表中插入数据来创建触发器,以将不同的数据插入到另一个表中.
I need to create a trigger off the insertion of data in one table to insert different data into another table.
在此触发器中,我需要根据原始表中新插入数据中的值将某些数据插入到新表中.我对此完全感到困惑.我以为我会很有创意并在值部分使用 case 语句,但它不起作用.
Within this trigger I need to insert certain data into the new table based upon values within the newly inserted data from the original table. I am totally confused on this. i thought I would be creative and use a case statement within teh Values section but it is not working.
有人可以帮我解决这个问题吗?(以下是我目前拥有的触发器代码)
Can anyone please help me on this? (below is the code for the trigger that I have as of now)
INSERT INTO dbo.WebOnlineUserPeopleDashboard
(
ONLINE_USERACCOUNT_ID,
ONLINE_ROOMS_DIRECTORY,
ONLINE_ROOMS_LIST,
ONLINE_ROOMS_PLACEMENT,
ONLINE_ROOMS_MANAGEMENT,
ONLINE_MAILINGLIST_DIRECTORY,
ONLINE_MAILINGLIST_LIST,
ONLINE_MAILINGLIST_MEMBERS,
ONLINE_MAILINGLIST_MANAGER,
ONLINE_PEOPLESEARCH_DIRECTORY
)
VALUES
IF (SELECT ONLINE_PEOPLE_FULL_ACCESS FROM INSERTED) = 1
BEGIN
SELECT
ONLINE_USERACCOUNT_ID,
1,
1,
1,
1,
1,
1,
1,
1,
1
FROM INSERTED
END
ELSE IF (SELECT ONLINE_PEOPLE_FULL_ACCESS FROM INSERTED) = 0
BEGIN
SELECT
ONLINE_USERACCOUNT_ID,
0,
0,
0,
0,
0,
0,
0,
0,
0
FROM INSERTED
END
ELSE
BEGIN
SELECT
ONLINE_USERACCOUNT_ID,
CASE --DIRECTORY
WHEN ONLINE_PEOPLE_ROOMS_PLACEMENT_FULL_ACCESS = 1 OR ONLINE_PEOPLE_ROOMS_PLACEMENT_VIEW = 1 OR ONLINE_PEOPLE_ROOMS_PLACEMENT_ADD = 1 OR ONLINE_PEOPLE_ROOMS_PLACEMENT_UPDATE = 1 OR ONLINE_PEOPLE_ROOMS_PLACEMENT_DELETE = 1
THEN 1
WHEN ONLINE_PEOPLE_ROOMS_PLACEMENT_FULL_ACCESS = 0
THEN 0
END,
CASE
WHEN ONLINE_PEOPLE_ROOMS_PLACEMENT_VIEW = 1
THEN 1
WHEN ONLINE_PEOPLE_ROOMS_PLACEMENT_VIEW = 0
THEN 0
END,
CASE
WHEN ONLINE_PEOPLE_ROOMS_PLACEMENT_ADD = 1 OR ONLINE_PEOPLE_ROOMS_PLACEMENT_UPDATE = 1 OR ONLINE_PEOPLE_ROOMS_PLACEMENT_DELETE = 1
THEN 1
WHEN ONLINE_PEOPLE_ROOMS_PLACEMENT_ADD = 0 AND ONLINE_PEOPLE_ROOMS_PLACEMENT_UPDATE = 0 AND ONLINE_PEOPLE_ROOMS_PLACEMENT_DELETE = 0
THEN 0
END,
CASE
WHEN ONLINE_PEOPLE_ROOMS_MANAGEMENT_FULL_ACCESS = 1
THEN 1
WHEN ONLINE_PEOPLE_ROOMS_MANAGEMENT_FULL_ACCESS = 0
THEN 0
END,
CASE
WHEN ONLINE_PEOPLE_MAILING_LISTS_FULL_ACCESS = 1 OR ONLINE_PEOPLE_MAILING_LISTS_VIEW = 1 OR ONLINE_PEOPLE_MAILING_LISTS_MEMBERS_ADD = 1 OR ONLINE_PEOPLE_MAILING_LISTS_MEMBERS_UPDATE = 1 OR ONLINE_PEOPLE_MAILING_LISTS_MEMBERS_DELETE = 1
THEN 1
WHEN ONLINE_PEOPLE_MAILING_LISTS_FULL_ACCESS = 0
THEN 0
END,
CASE
WHEN ONLINE_PEOPLE_MAILING_LISTS_VIEW = 1
THEN 1
WHEN ONLINE_PEOPLE_MAILING_LISTS_VIEW = 0
THEN 0
END,
CASE
WHEN ONLINE_PEOPLE_MAILING_LISTS_MEMBERS_ADD = 1 OR ONLINE_PEOPLE_MAILING_LISTS_MEMBERS_UPDATE = 1 OR ONLINE_PEOPLE_MAILING_LISTS_MEMBERS_DELETE = 1
THEN 1
WHEN ONLINE_PEOPLE_MAILING_LISTS_MEMBERS_ADD = 0 AND ONLINE_PEOPLE_MAILING_LISTS_MEMBERS_UPDATE = 0 AND ONLINE_PEOPLE_MAILING_LISTS_MEMBERS_DELETE = 0
THEN 0
END,
CASE
WHEN ONLINE_PEOPLE_MAILING_LISTS_ADD = 1 OR ONLINE_PEOPLE_MAILING_LISTS_UPDATE = 1 OR ONLINE_PEOPLE_MAILING_LISTS_DELETE = 1
THEN 1
WHEN ONLINE_PEOPLE_MAILING_LISTS_ADD = 1 OR ONLINE_PEOPLE_MAILING_LISTS_UPDATE = 1 OR ONLINE_PEOPLE_MAILING_LISTS_DELETE = 1
THEN 0
END,
CASE
WHEN ONLINE_PEOPLE_PEOPLE_SEARCH = 1
THEN 1
WHEN ONLINE_PEOPLE_PEOPLE_SEARCH = 0
THEN 0
END
FROM INSERTED
END
END
推荐答案
类似的事情?
IF (SELECT ONLINE_PEOPLE_FULL_ACCESS FROM INSERTED) = 1
INSERT INTO dbo.WebOnlineUserPeopleDashboard
(
ONLINE_USERACCOUNT_ID,
ONLINE_ROOMS_DIRECTORY,
ONLINE_ROOMS_LIST,
ONLINE_ROOMS_PLACEMENT,
ONLINE_ROOMS_MANAGEMENT,
ONLINE_MAILINGLIST_DIRECTORY,
ONLINE_MAILINGLIST_LIST,
ONLINE_MAILINGLIST_MEMBERS,
ONLINE_MAILINGLIST_MANAGER,
ONLINE_PEOPLESEARCH_DIRECTORY
)
VALUES
SELECT
ONLINE_USERACCOUNT_ID,
1,
1,
1,
1,
1,
1,
1,
1,
1
FROM INSERTED
ELSE IF (SELECT ONLINE_PEOPLE_FULL_ACCESS FROM INSERTED) = 0
INSERT INTO dbo.WebOnlineUserPeopleDashboard
(
ONLINE_USERACCOUNT_ID,
ONLINE_ROOMS_DIRECTORY,
ONLINE_ROOMS_LIST,
ONLINE_ROOMS_PLACEMENT,
ONLINE_ROOMS_MANAGEMENT,
ONLINE_MAILINGLIST_DIRECTORY,
ONLINE_MAILINGLIST_LIST,
ONLINE_MAILINGLIST_MEMBERS,
ONLINE_MAILINGLIST_MANAGER,
ONLINE_PEOPLESEARCH_DIRECTORY
)
VALUES
SELECT
ONLINE_USERACCOUNT_ID,
0,
0,
0,
0,
0,
0,
0,
0,
0
FROM INSERTED
ELSE
INSERT INTO dbo.WebOnlineUserPeopleDashboard
(
ONLINE_USERACCOUNT_ID,
ONLINE_ROOMS_DIRECTORY,
ONLINE_ROOMS_LIST,
ONLINE_ROOMS_PLACEMENT,
ONLINE_ROOMS_MANAGEMENT,
ONLINE_MAILINGLIST_DIRECTORY,
ONLINE_MAILINGLIST_LIST,
ONLINE_MAILINGLIST_MEMBERS,
ONLINE_MAILINGLIST_MANAGER,
ONLINE_PEOPLESEARCH_DIRECTORY
)
SELECT
ONLINE_USERACCOUNT_ID,
CASE --DIRECTORY
WHEN ONLINE_PEOPLE_ROOMS_PLACEMENT_FULL_ACCESS = 1 OR ONLINE_PEOPLE_ROOMS_PLACEMENT_VIEW = 1 OR ONLINE_PEOPLE_ROOMS_PLACEMENT_ADD = 1 OR ONLINE_PEOPLE_ROOMS_PLACEMENT_UPDATE = 1 OR ONLINE_PEOPLE_ROOMS_PLACEMENT_DELETE = 1
THEN 1
WHEN ONLINE_PEOPLE_ROOMS_PLACEMENT_FULL_ACCESS = 0
THEN 0
END,
CASE
WHEN ONLINE_PEOPLE_ROOMS_PLACEMENT_VIEW = 1
THEN 1
WHEN ONLINE_PEOPLE_ROOMS_PLACEMENT_VIEW = 0
THEN 0
END,
CASE
WHEN ONLINE_PEOPLE_ROOMS_PLACEMENT_ADD = 1 OR ONLINE_PEOPLE_ROOMS_PLACEMENT_UPDATE = 1 OR ONLINE_PEOPLE_ROOMS_PLACEMENT_DELETE = 1
THEN 1
WHEN ONLINE_PEOPLE_ROOMS_PLACEMENT_ADD = 0 AND ONLINE_PEOPLE_ROOMS_PLACEMENT_UPDATE = 0 AND ONLINE_PEOPLE_ROOMS_PLACEMENT_DELETE = 0
THEN 0
END,
CASE
WHEN ONLINE_PEOPLE_ROOMS_MANAGEMENT_FULL_ACCESS = 1
THEN 1
WHEN ONLINE_PEOPLE_ROOMS_MANAGEMENT_FULL_ACCESS = 0
THEN 0
END,
CASE
WHEN ONLINE_PEOPLE_MAILING_LISTS_FULL_ACCESS = 1 OR ONLINE_PEOPLE_MAILING_LISTS_VIEW = 1 OR ONLINE_PEOPLE_MAILING_LISTS_MEMBERS_ADD = 1 OR ONLINE_PEOPLE_MAILING_LISTS_MEMBERS_UPDATE = 1 OR ONLINE_PEOPLE_MAILING_LISTS_MEMBERS_DELETE = 1
THEN 1
WHEN ONLINE_PEOPLE_MAILING_LISTS_FULL_ACCESS = 0
THEN 0
END,
CASE
WHEN ONLINE_PEOPLE_MAILING_LISTS_VIEW = 1
THEN 1
WHEN ONLINE_PEOPLE_MAILING_LISTS_VIEW = 0
THEN 0
END,
CASE
WHEN ONLINE_PEOPLE_MAILING_LISTS_MEMBERS_ADD = 1 OR ONLINE_PEOPLE_MAILING_LISTS_MEMBERS_UPDATE = 1 OR ONLINE_PEOPLE_MAILING_LISTS_MEMBERS_DELETE = 1
THEN 1
WHEN ONLINE_PEOPLE_MAILING_LISTS_MEMBERS_ADD = 0 AND ONLINE_PEOPLE_MAILING_LISTS_MEMBERS_UPDATE = 0 AND ONLINE_PEOPLE_MAILING_LISTS_MEMBERS_DELETE = 0
THEN 0
END,
CASE
WHEN ONLINE_PEOPLE_MAILING_LISTS_ADD = 1 OR ONLINE_PEOPLE_MAILING_LISTS_UPDATE = 1 OR ONLINE_PEOPLE_MAILING_LISTS_DELETE = 1
THEN 1
WHEN ONLINE_PEOPLE_MAILING_LISTS_ADD = 1 OR ONLINE_PEOPLE_MAILING_LISTS_UPDATE = 1 OR ONLINE_PEOPLE_MAILING_LISTS_DELETE = 1
THEN 0
END,
CASE
WHEN ONLINE_PEOPLE_PEOPLE_SEARCH = 1
THEN 1
WHEN ONLINE_PEOPLE_PEOPLE_SEARCH = 0
THEN 0
END
FROM INSERTED
这篇关于在 Insert 语句的值部分使用 Case 语句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!