Post reply

update trigger on table - need help

  • August 14, 2007 at 1:08 pm

    #175986

    Hi

    I have two tables SVC00610 and SVC00315

    User will update SVC00610 from front end application, system should check if any update / insert on SVC00610, trigger should run to update SVC00315 table.

    I need help writing trigger on SVC00610 table.'

    Table: SVC00610
    EQUIPID ACTIVE SCHEDID
    71219 0          
    156227 0          
    156227 1 FX3      
    156674 0 FX3      
    156679 0 FX3      
    156704 0 FX3      
    Table: SVC00315
    EQUIPID ACTIVE SCHEDID
    71219 0          
    156227 0          
    156227 1 FX3      
    156674 0 FX3      
    156679 0 FX3      
    156704 0 FX3      

    Thank you,

    Vijay

  • August 14, 2007 at 1:38 pm

    #726901

    Could we see what you have done already in this regard?

  • August 14, 2007 at 1:44 pm

    #726903

    Hi

    Nothing done on the 2 tables.

    right now front end application is updating both tables with different windows.

    so i need to write trigger on SVC00610 if any Insert / Update / Delete, it should do the same in SVC00315

    thx

    Vijay

     

     

  • August 14, 2007 at 2:50 pm

    #726926

    So, you are asking us on this forum to write your triggers for you instead of writing them yourself?  I'd like to see what you have tried to come up with first, and then help you from there.  Triggers are not difficult to write, even though there could be a few gotchas if your aren't careful.

    Be sure to read BOL, it is quite helpful.

  • August 15, 2007 at 9:03 am

    #727071

    Hi Lynn Pettis

    thx for the response

    completed writing triggers.

    CREATE

     TRIGGER [dbo].[Insert1] ON [dbo].[SVC00610]

    FOR

    INSERT

    AS

    BEGIN

    DELETE SVC00315

    FROM inserted WHERE SVC00315.EQUIPID = inserted.EQUIPID AND SVC00315.SCHEDID = inserted.SCHEDID

    INSERT INTO SVC00315

    SELECT EQUIPID, ACTIVE, SCHEDID FROM inserted

    END

    --------------

    CREATE

     TRIGGER [dbo].[Update1] ON [dbo].[SVC00610]

    FOR

    UPDATE

    AS

    BEGIN

    DELETE SVC00315

    FROM deleted WHERE SVC00315.EQUIPID = deleted.EQUIPID AND SVC00315.SCHEDID = deleted.SCHEDID

    INSERT INTO SVC00315

    SELECT EQUIPID, ACTIVE, SCHEDID FROM inserted

    END

    --------------

    CREATE

     TRIGGER [dbo].[Delete1] ON [dbo].[SVC00610]

    FOR

    DELETE

    AS

    BEGIN

    DELETE SVC00315

    FROM deleted WHERE SVC00315.EQUIPID = deleted.EQUIPID AND SVC00315.SCHEDID = deleted.SCHEDID

    END

    Thx

    Vijay

  • August 15, 2007 at 9:14 am

    #727082

    Have you tested these triggers in a development environment?

    The next question, is there a possibility of more than one record being inserted/updated/deleted at a time from the application?

  • August 15, 2007 at 9:43 am

    #727102

    Hi Lynn Pettis

    Yes tested and it is working fine.

    only one record inserted from the front end at a time.

    Thx

    Vijay

     

  • August 15, 2007 at 9:54 am

    #727105

    You indicate that the insert trigger works, how about the others?  If all is fine, I think you have successfully done what you set out to do.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply