Using a Case statement within the values section of an Insert statement

Posted by mattgcon on Stack Overflow See other posts from Stack Overflow or by mattgcon
Published on 2010-04-15T21:47:54Z Indexed on 2010/04/15 22:13 UTC
Read the original article Hit count: 631

Filed under:
|
|
|

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

© Stack Overflow or respective owner

Related posts about sql

Related posts about update