一千萬個為什麽

搜索

在Insert語句的values部分中使用Case語句

請原諒我的無知和糟糕的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

最佳答案

如果觸發器影響多行,這將處理所有行:

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)

轉載註明原文: 在Insert語句的values部分中使用Case語句