Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2011
    Posts
    63

    Unanswered: Trigger to Insert/Update Table Column Based on Value of Another Table

    I want to create a trigger so that whenever there is an update, insert, or delete to a table called tbl_Audit, it will call a trigger to update another table called tbl_AuditPlan. The challenge is that I need to insert into a specific column based upon a column from a view called v_AuditInfo.

    For example, if an audit is scheduled for Quarter 3, 2009 then I want to insert/update a record into tbl_AuditPlan.Q3, tbl_AuditPlan.Year. Then if there is another audit scheduled for Quarter 4, 2009 then I want it to insert/update tbl_AuditPlan.Q4, tbl_AuditPlan.year.

    I need to format tblAuditPlan so that it appears like:
    Group-- Q1 -----Q2 ------ Q3 ------ Q4 ------Year
    A| Audit Info| Audit Info| Audit Info| Audit Info| 2011
    B| Audit Info| Audit Info| Audit Info| Audit Info| 2011
    C| Audit Info| Audit Info| Audit Info| Audit Info| 2011


    The table layout of v_AuditInfo currently looks like
    ID | Quarter | Year | Group | Audit Info|

    I have a bit of code below, but it's not doing so well


    Code:
    CREATE TRIGGER UpdateAuditPlan
    ON dbo.tbl_Audit
    AFTER UPDATE,INSERT,DELETE
    AS
    
    
    Select 
    	case v_AuditInfo
    		when v_AuditInfo.ScheduledQuarter ='Q1' Then INSERT INTO tbl_AuditPlan (Q1, ScheduledYear,Group_Name) Select AuditInfo,Group_Name,ScheduledYear FROM v_AuditInfo
    		when v_AuditInfo.ScheduledQuarter ='Q2' Then INSERT INTO tbl_AuditPlan (Q2, ScheduledYear,Group_Name) Select AuditInfo,Group_Name,ScheduledYear FROM v_AuditInfo
    		when v_AuditInfo.ScheduledQuarter ='Q3' Then INSERT INTO tbl_AuditPlan (Q3, ScheduledYear,Group_Name) Select AuditInfo,Group_Name,ScheduledYear FROM v_AuditInfo
    		when v_AuditInfo.ScheduledQuarter ='Q4' Then INSERT INTO tbl_AuditPlan (Q4, ScheduledYear,Group_Name) Select AuditInfo,Group_Name,ScheduledYear FROM v_AuditInfo
    	END
    FROM  v_AuditInfo
    Last edited by tkepongo; 08-01-11 at 19:59.

  2. #2
    Join Date
    Aug 2011
    Posts
    12

    Answer

    Hi,
    I found one way that I examined in Sql server 2008 enterprise edition,
    and it worked correctly.
    And that is below:

    CREATE TRIGGER UpdateAuditPlan
    ON dbo.tbl_Audit
    AFTER UPDATE,INSERT,DELETE
    AS


    Select
    (Case ScheduledQuarter
    When 'Q1' Then
    dbo.fn_tbl_AuditPlan(Q1, ScheduledYear,Group_Name) --I put the insert statement in this function and pass my parameters to it
    Else
    Case ScheduledQuarter
    When 'Q2' Then
    dbo.fn_tbl_AuditPlan(Q2, ScheduledYear,Group_Name)
    End
    end)
    From v_AuditInfo

    --for 'Q3' And 'Q4' you can do the same


    I hope this works for you.

  3. #3
    Join Date
    Jul 2011
    Posts
    63
    Quote Originally Posted by madadi028 View Post
    Hi,
    I found one way that I examined in Sql server 2008 enterprise edition,
    and it worked correctly.
    And that is below:

    CREATE TRIGGER UpdateAuditPlan
    ON dbo.tbl_Audit
    AFTER UPDATE,INSERT,DELETE
    AS


    Select
    (Case ScheduledQuarter
    When 'Q1' Then
    dbo.fn_tbl_AuditPlan(Q1, ScheduledYear,Group_Name) --I put the insert statement in this function and pass my parameters to it
    Else
    Case ScheduledQuarter
    When 'Q2' Then
    dbo.fn_tbl_AuditPlan(Q2, ScheduledYear,Group_Name)
    End
    end)
    From v_AuditInfo

    --for 'Q3' And 'Q4' you can do the same


    I hope this works for you.
    Hi, thanks for the help! I tried the code but I get the following error:

    Code:
    Cannot find either column "dbo" or the user-defined function or aggregate "dbo.fn_tbl_AuditPlan", or the name is ambiguous.
    Did you create a function? If so, can you provide me the code for that? Thanks!!

  4. #4
    Join Date
    Jul 2011
    Posts
    63
    I got it working with the following code:

    Thanks for the help! I have another help request on the forum if you'd like to help

    Code:
    CREATE TRIGGER UpdateAuditPlan
    ON dbo.tbl_Audit
    AFTER UPDATE,INSERT,DELETE
    AS
    BEGIN
    INSERT INTO tbl_AuditPlan (Q1,Q2,Q3,Q4,Group_Name, ScheduledYear) 
    Select case 
    		when v_AuditInfo.ScheduledQuarter ='Q1' Then AuditInfo else null end,
    case 
    		when v_AuditInfo.ScheduledQuarter ='Q2' Then AuditInfo else null end,
    
    case 
    		when v_AuditInfo.ScheduledQuarter ='Q3' Then AuditInfo else null end,
    
    case 
    		when v_AuditInfo.ScheduledQuarter ='Q4' Then AuditInfo else null end,
    
    Group_Name,ScheduledYear 
    FROM v_AuditInfo
    END

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •