Triggers SQL Server (Please Help!)

Question

Triggers SQL Server (Please Help!)

Jason Willis

SSC Veteran

Points: 225
More actions
July 1, 2004 at 3:59 pm

#102090

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!

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

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

Phil Parkin SSC Guru Points: 246965 More actions · Answer 1

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

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Dan Smith SSC Enthusiast Points: 152 More actions · Answer 2

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?

Jason Willis SSC Veteran Points: 225 More actions · Answer 3

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

Dan Smith SSC Enthusiast Points: 152 More actions · Answer 4

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

FROM inserted i

Does that cure it?

Jason Willis SSC Veteran Points: 225 More actions · Answer 5

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

Dan Smith SSC Enthusiast Points: 152 More actions · Answer 6

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?

Jason Willis SSC Veteran Points: 225 More actions · Answer 7

Dan I tried it again and it WORKED!!!

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

Dan Smith SSC Enthusiast Points: 152 More actions · Answer 8

Dan Smith

SSC Enthusiast

Points: 152

July 2, 2004 at 6:54 am

#513141

No problems, glad to help.