Triggers SQL Server (Please Help!)

  • I have two tables within a database Ticket and TicketH. I have an Insert and Update Trigger that copies the record in Ticket to TicketH. There is only one difference in Ticket and TicketH, TicketH has a seqNbr field. I want this field to increment based on the TicketID for example:

    TicketID (Ticket Table)

    1000

    TicketID (TicketH Table) seqNbr(TicketH)

    1000                                        0           Insert

    1000                                        1           Update

    1000                                        2           Update

    In need this to run either as part of the trigger or a UDF that can run at the beginng of the Trigger.

    Thank you in advance!

  • Not sure that I follow the process. Do you want to automatically create a record in TicketH whenever one is added or amended in Ticket? Is SeqNbr just an ascending field within each TicketID? Can you post your existing trigger here?

    Cheers

    Phil


  • So basically then TicketH is to be an audit trail of inserts and updates done to the Ticket table and both the Insert and Update triggers will add a new record to TicketH, correct?

    If so then both the Insert and Update triggers should be the same and just have something like;

    INSERT INTO TicketH ( TicketID, SeqNbr, <other fields> )
    SELECT TicketID,
               (IsNull((Select Max(SeqNbr) From TicketH h Where h.TicketID = i.TicketID), 0)+1),
               <other fields>
    FROM inserted i

    Does that cover it?

  • Dan,

    That looks like exactly what I need, but I am getting a syntax error somewhere near the from clause. Below is the trigger.

    ********Update Trigger with auto increment

    CREATE TRIGGER [TicketHUP] ON [dbo].[Ticket]

    AFTER UPDATE

    AS

    INSERT TicketH (TicketID, SeqNbr, Priority,  NAMEL, NAMEF, NetName, PCSerial,

                    Room, WD, Ring, NetworkPrinter_1, ZUSER, PIN, MMS, AMS,

                    PH_NO, PH_EXT, Date_Recieved, Problem_Description,

                    Problem_Category, CapersTktNum, Problem_Tech_Update,

                    Problem_Update_Date, Closed, Date_Closed, Closed_By,

                    Res_Code, Office, SPA, EmailFLG, Tech_Queue, Assigned_By,

                    HDUser, Sub_Category)

    SELECT TicketID, (IsNull((Select Max(SeqNbr) From TicketH h Where h.TicketID = i.TicketID), 0)+1),

           Priority, NAMEL, NAMEF, NetName, PCSerial, Room, WD, Ring, NetworkPrinter_1,

           ZUSER, PIN, MMS, AMS, PH_NO, PH_EXT, Date_Recieved, Problem_Description,

           Problem_Category, CapersTktNum, Problem_Tech_Update, Problem_Update_Date,

           Closed, Date_Closed, Closed_By, Res_Code, Office, SPA, EmailFLG,

           Tech_Queue, Assigned_By, HDUser, Sub_Category

    FROM inserted

  • You have missed the alias for the "inserted" table;

    FROM inserted i

    Does that cure it?

  • I tried it that way too, that doesn't work either I know it has to be something small I am missing.

  • Ok, well the alias "i" must be included as that is being referenced to get the maximum sequence number in this step:

    (IsNull((Select Max(SeqNbr) From TicketH h Where h.TicketID = i.TicketID), 0)+1)

    What is the error you are getting?

  • Dan I tried it again and it WORKED!!!

    Dan your the man!!! Thank you so much!

  • No problems, glad to help.

Viewing 9 posts - 1 through 9 (of 9 total)

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