在 Insert 语句的值部分中使用 Case 语句

发布于 2024-08-29 05:56:08 字数 4493 浏览 5 评论 0原文

请原谅我的无知和糟糕的 SQL 编程技能，但我通常是一个基本的 SQL 开发人员。

我需要在一个表中插入数据时创建一个触发器，以将不同的数据插入到另一个表中。

在此触发器中，我需要根据原始表中新插入的数据中的值将某些数据插入新表中。我对此完全困惑。我以为我会很有创意，并在“值”部分中使用案例陈述，但它不起作用。

有人可以帮我解决这个问题吗？（下面是我现在拥有的触发器的代码）

    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

原文

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.

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

枕梦 2024-09-05 05:56:08

如果多行受触发器影响，这将处理所有行：

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,
    1,
    1,
    1,
    1,
    1,
    1,
    1,
    1,
    1
FROM INSERTED
WHERE ONLINE_PEOPLE_FULL_ACCESS=1
UNION
SELECT
    ONLINE_USERACCOUNT_ID,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0
FROM INSERTED
WHERE ONLINE_PEOPLE_FULL_ACCESS=0
UNION
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
        --ELSE ???  what is the default
    END,
    CASE
        WHEN ONLINE_PEOPLE_ROOMS_PLACEMENT_VIEW = 1
            THEN 1
        WHEN ONLINE_PEOPLE_ROOMS_PLACEMENT_VIEW = 0 
            THEN 0
        --ELSE ???  what is the default
    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
        --ELSE ???  what is the default
    END,
    CASE
        WHEN ONLINE_PEOPLE_ROOMS_MANAGEMENT_FULL_ACCESS = 1
            THEN 1
        WHEN ONLINE_PEOPLE_ROOMS_MANAGEMENT_FULL_ACCESS = 0
            THEN 0
        --ELSE ???  what is the default
    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
        --ELSE ???  what is the default
    END,
    CASE
        WHEN ONLINE_PEOPLE_MAILING_LISTS_VIEW = 1
            THEN 1
        WHEN ONLINE_PEOPLE_MAILING_LISTS_VIEW = 0
            THEN 0
        --ELSE ???  what is the default
    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
        --ELSE ???  what is the default
    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
        --ELSE ???  what is the default
    END,
    CASE
        WHEN ONLINE_PEOPLE_PEOPLE_SEARCH = 1
            THEN 1
        WHEN ONLINE_PEOPLE_PEOPLE_SEARCH = 0
            THEN 0
        --ELSE ???  what is the default
    END
FROM INSERTED
WHERE ONLINE_PEOPLE_FULL_ACCESS NOT IN (0,1)

this will handle all rows if multiple rows are affected by the trigger:

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,
    1,
    1,
    1,
    1,
    1,
    1,
    1,
    1,
    1
FROM INSERTED
WHERE ONLINE_PEOPLE_FULL_ACCESS=1
UNION
SELECT
    ONLINE_USERACCOUNT_ID,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0,
    0
FROM INSERTED
WHERE ONLINE_PEOPLE_FULL_ACCESS=0
UNION
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
        --ELSE ???  what is the default
    END,
    CASE
        WHEN ONLINE_PEOPLE_ROOMS_PLACEMENT_VIEW = 1
            THEN 1
        WHEN ONLINE_PEOPLE_ROOMS_PLACEMENT_VIEW = 0 
            THEN 0
        --ELSE ???  what is the default
    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
        --ELSE ???  what is the default
    END,
    CASE
        WHEN ONLINE_PEOPLE_ROOMS_MANAGEMENT_FULL_ACCESS = 1
            THEN 1
        WHEN ONLINE_PEOPLE_ROOMS_MANAGEMENT_FULL_ACCESS = 0
            THEN 0
        --ELSE ???  what is the default
    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
        --ELSE ???  what is the default
    END,
    CASE
        WHEN ONLINE_PEOPLE_MAILING_LISTS_VIEW = 1
            THEN 1
        WHEN ONLINE_PEOPLE_MAILING_LISTS_VIEW = 0
            THEN 0
        --ELSE ???  what is the default
    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
        --ELSE ???  what is the default
    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
        --ELSE ???  what is the default
    END,
    CASE
        WHEN ONLINE_PEOPLE_PEOPLE_SEARCH = 1
            THEN 1
        WHEN ONLINE_PEOPLE_PEOPLE_SEARCH = 0
            THEN 0
        --ELSE ???  what is the default
    END
FROM INSERTED
WHERE ONLINE_PEOPLE_FULL_ACCESS NOT IN (0,1)

回复收藏 0 原文

埋情葬爱 2024-09-05 05:56:08

像这样的东西吗？

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

Something like this?

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

回复收藏 0 原文

~没有更多了~