Need Help Creating ON INSERT trigger in SQL 2008

Hello, I've written the following pseudo code to solve my need, and I need to have this implemented on the server as a trigger, such that any time a record is added to the bookings table (tblTripClient) are of the type "ST" and if this is not the first booking, but if it IS recordcount mod 10 = 0 then some additional records would be added and linked in.

My pseudo sql looks like this:
ON INSERT INTO tblTripClient
[valTripNo] = tblTripClient.ClientNo
[valBookingsCount] = (SELECT COUNT(*) FROM tblTripClient WHERE TripNo = [valTripNo])
[valBookingID] = tblTripClient.TripClient
[valTripType] = (SELECT TripType FROM tblTrips WHERE TripNo = [valTripNo])

IF tblTrips.TripType = “ST” Then
	IF Bookings.Count MOD 10 = 0 Then
•	INSERT INTO tblStudentTrip SET StudentLast = ‘Chaperone’ AND StudentFirst = ‘Adult’
o	Set Additional fields, too! This includes TripNo… This will now be used to flag the tripno that the chaperone is for, and will be visible on the Kelly Tours client/travelers page, along with the “booking” at 0 use cost
o	[valTravelerID] = tblStudentTrip.ID
•	INSERT INTO tblTraveler_lkp SET ClientID = 42859 AND TravelerID = [valTravelerID] AND FamilialRelation = ‘Teacher/Chaperone’
•	INSERT INTO tblRoomingLists SET TripClient = [valBookingID] AND TravelerID = [valTravelerID] AND RoomNumber = 0 AND HotelNumber = 0 AND BusNumber = 1 AND ListName = ‘Traveler Manifest’

Open in new window


Could somebody please help me to get this converted and usable? Your help is much appreciated!
LVL 3
Jon JaquesInformation TechnologistAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
Something like below.  You always want to use set-based processing rather than row-by-row within a trigger.


CREATE TRIGGER tblTripClient__TRG_INS
ON dbo.tblTripClient
AFTER INSERT
AS
SET NOCOUNT ON
DECLARE @rows_to_process TABLE (
    TripNo int PRIMARY KEY,
    BookingId int NULL
    )

INSERT INTO @rows_to_process ( TripNo )
SELECT i.TripNo
FROM inserted i
INNER JOIN dbo.tblTripClient tc ON
    tc.TripNo = i.TripNo
WHERE
    i.TripType = 'ST'
GROUP BY
    i.TripNo
HAVING COUNT(*) % 10 = 0

IF EXISTS(SELECT 1 FROM @rows_to_process)
BEGIN
    INSERT INTO dbo.tblStudentTrip ( ..., StudentLast, StudentFirst )
    SELECT ..., 'Chapeone', 'Adult'
    FROM @rows_to_process
•      INSERT INTO tblTraveler_lkp
    SELECT ...
    FROM @rows_to_process
    INSERT INTO tblRoomingLists
    SELECT ...
    FROM @rows_to_process
END
GO
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jon JaquesInformation TechnologistAuthor Commented:
Wow, Scott, I am so humbled and impressed! LOL!!! This is why I came and looked for an expert before I went and bashed my head against a wall while creating an RBAR nightmare!

As I read it, it seems to match my criteria perfectly! I never would have thought to use the temp table in the middle, that's awesome! I'll set it up and try throwing some test data at it!
0
Jon JaquesInformation TechnologistAuthor Commented:
Okay, working out the bottom third, not sure how to complete this:

IF EXISTS(SELECT 1 FROM @rows_to_process)
BEGIN
    INSERT INTO dbo.tblStudentTrip (StudentLast, StudentFirst, DEDate, DMDate) VALUES ("Chaperone", "Adult", GetDate(), GetDate())
      
    SELECT ..., 'Chapeone', 'Adult'
   
      FROM @rows_to_process
•      INSERT INTO tblTraveler_lkp (ClientID, TravelerID, FamilialRelation) VALUES (42859, @@tblStudentTrip.ID@@, "Chaperone")
   
      SELECT ...
    FROM @rows_to_process
   
      INSERT INTO tblRoomingLists (TripClient, TravelerID, RoomNumber, HotelNumber, BusNumber, ListName) VALUES (@rows_to_process.BookingId, @@tblStudentTrip.ID@@, 0, 1, 1, "Traveler Manifest")
   
      SELECT ...
    FROM @rows_to_process
END
GO
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Jon JaquesInformation TechnologistAuthor Commented:
Okay, I realized that my specification was off by just a tiny bit, being that a new record also needs to be created in the table that the trigger is on, and I've updated the trigger as below; I have no idea if I'm accessing the returned values correctly in the bottom third of the trigger, so if anyone can check that and/or correct it, that would be awesome!

CREATE TRIGGER tblTripClient__TRG_INS
ON dbo.tblTripClient
AFTER INSERT
AS
SET NOCOUNT ON
DECLARE @rows_to_process TABLE (
    TripNo int PRIMARY KEY,
      TravelerId Int NULL,
      BookingId Int NULL
    )

INSERT INTO @rows_to_process ( TripNo )
SELECT i.TripNo
FROM inserted i
INNER JOIN dbo.tblTripClient tc ON
    tc.TripNo = i.TripNo
WHERE
    i.TripType = 'Student (ST)'
GROUP BY
    i.TripNo
HAVING COUNT(*) % 10 = 0

IF EXISTS(SELECT 1 FROM @rows_to_process)
BEGIN
      /* Add a new chaperone traveler to the database */
    INSERT INTO dbo.tblStudentTrip (StudentLast, StudentFirst, DEDate, DMDate) VALUES ("Chaperone", "Adult", GetDate(), GetDate())
      UPDATE @rows_to_process SET TravelerID = inserted.ID
      
      /* Link the new traveler to a constant, fixed, client account for easy tracking */
      SELECT TravelerID
    FROM @rows_to_process
            INSERT INTO tblTraveler_lkp (ClientID, TravelerID, FamilialRelation) VALUES (42859, @rows_to_process.TravelerID, "Chaperone")
   
      /* Now add a new booking to reserve a seat for the new chaperone traveler */
      SELECT TripNo, TravelerID
    FROM @rows_to_process    
            INSERT INTO tblTripClient (TripNo, ClientNo, TravelerID, CostToUse, DEDate, DMDate) VALUES (@rows_to_process.TripNo, 42859, @rows_to_process.TravelerID, 0, GetDate(), GetDate())
      UPDATE @rows_to_process SET BookingID = inserted.TripClient
   
      /* Finally, link chaperone traveler to booking via the rooming list table */
      SELECT TripClient, TravelerID
    FROM @rows_to_process    
            INSERT INTO tblRoomingLists (TripClient, TravelerID, RoomNumber, HotelNumber, BusNumber, ListName) VALUES (@rows_to_process.BookingId, @rows_to_process.TravelerID, 0, 1, 1, "Traveler Manifest")
   
END
GO
0
Jon JaquesInformation TechnologistAuthor Commented:
I think I'm almost there... something like this?

CREATE TRIGGER tblTripClient__TRG_INS
ON dbo.tblTripClient
AFTER INSERT
AS
SET NOCOUNT ON
DECLARE @rows_to_process TABLE (
    TripNo int PRIMARY KEY
    )
DECLARE @TripNo Int NULL
DECLARE @TravelerId Int NULL
DECLARE @BookingId Int NULL


INSERT INTO @rows_to_process ( TripNo )
SELECT i.TripNo
FROM inserted i
INNER JOIN dbo.tblTripClient tc ON
    tc.TripNo = i.TripNo
WHERE
    i.TripType = 'Student (ST)'
GROUP BY
    i.TripNo
HAVING COUNT(*) % 10 = 0

IF EXISTS(SELECT 1 FROM @rows_to_process)
BEGIN
	/* Fetch the TripNo... */
	SET @TripNo = SELECT TripNo FROM @rows_to_process;

	/* Add a new chaperone traveler to the database */
    INSERT INTO dbo.tblStudentTrip (StudentLast, StudentFirst, DEDate, DMDate) VALUES ("Chaperone", "Adult", GetDate(), GetDate())
	SET @TravelerID = SELECT SCOPE_IDENTITY();
	
	/* Link the new traveler to a constant, fixed, client account for easy tracking */
	INSERT INTO tblTraveler_lkp (ClientID, TravelerID, FamilialRelation) VALUES (42859, @TravelerID, "Chaperone")
    
	/* Now add a new booking to reserve a seat for the new chaperone traveler */
	INSERT INTO tblTripClient (TripNo, ClientNo, TravelerID, CostToUse, DEDate, DMDate) VALUES (@TripNo, 42859, @TravelerID, 0, GetDate(), GetDate())
	SET @BookingID = SELECT SCOPE_IDENTITY();
    
	/* Finally, link chaperone traveler to booking via the rooming list table */
	INSERT INTO tblRoomingLists (TripClient, TravelerID, RoomNumber, HotelNumber, BusNumber, ListName) VALUES (@BookingId, @TravelerID, 0, 1, 1, "Traveler Manifest")
    
END
GO

Open in new window

0
Vadim RappCommented:
if exists(
select 1 from tblTripClient join inserted on inserted.clientid=tblTripClient,clientid -- same client...
and tbltripclient.tripno < inserted.tripno -- not this client's first booking, there were other trips...

or exists(select 1 from tblTripClient where inserted.clientid=tblTripClient,clientid
and /*  recordcount mod 10 = 0 - this I did not quite get; recordcount of what? maybe you could explain in plain english: we want to add records to other tables if this client either already booked other trips in the past, or (what?)
*/
begin
insert into related_table(client_id, other_fields)
select top 1 client_id, other_values from inserted
(...)
end
0
Jon JaquesInformation TechnologistAuthor Commented:
Hello, we're looking at the total number of bookings (tblTripClient) for the given TripNo; for every 10 bookings, a chaperone booking is automatically entered, initially connected to a fixed ClientId, but later application logic allows that client to be assigned to someone else. As I count it, a total of 5 records must be added in order to make this work... 4 in the version above, but I found a new dependency that I hadn't anticipated.

Given that new change, I've got something like this for the work of inserts:
BEGIN
	--  Fetch the TripNo... 
	SELECT @TripNo = TripNo FROM @rows_to_process;

	--  Add a new chaperone traveler to the database 
	INSERT INTO dbo.tblStudentTrip (StudentLast, StudentFirst, DEDate, DMDate) VALUES ("Chaperone", "Adult", GetDate(), GetDate())
	SET @TravelerID = SCOPE_IDENTITY();
	
	--  Link the new traveler to a constant, fixed, client account for easy tracking 
	INSERT INTO tblTraveler_lkp (ClientID, TravelerID, FamilialRelation) VALUES (42859, @TravelerID, "Chaperone")

	-- Now, before we can insert the booking, we have to verify if there's a Chaperone
	-- price option on this trip, and if not, we must add it...
	SELECT @PricingId = TripPriceID FROM tblTripPrices;
	IF @PricingId = 0 
		INSERT INTO tblTripPrices (TripNo, TripPriceLabelID, TripPrice, TripDeposit, DEDate, DMDate) 
			VALUES (@TripNo, 114, 0, 0, GETDATE(), GETDATE())
    
	--  Now add a new booking to reserve a seat for the new chaperone traveler 
	INSERT INTO tblTripClient (TripNo, TripPriceID, ClientNo, TravelerID, CostToUse, TicketsOnly, TravelerID, DateBooked, DEDate, DMDate) 
		VALUES (@TripNo, @PricingId, 42859, @TravelerID, 0, 0, 1, GetDate(), GetDate(), GetDate())
	
	SET @BookingID = SCOPE_IDENTITY();
    
	--  Finally, link chaperone traveler to booking via the rooming list table 
	INSERT INTO tblRoomingLists (TripClient, TravelerID, RoomNumber, HotelNumber, BusNumber, ListName) VALUES (@BookingId, @TravelerID, 0, 1, 1, "Traveler Manifest")
    
END

Open in new window

0
Jon JaquesInformation TechnologistAuthor Commented:
This was definitely the base for my new trigger, and while I'm not done with it, it has now grown so complex I realize that it is beyond the scope of this thread.

Thanks for your help, Scott!
0
Scott PletcherSenior DBACommented:
You're so welcome!

Sorry, but I've been really busy, not able to get back for too much follow-up, which I'm normally pretty good about doing.
0
Vadim RappCommented:
if it was my code, I would look at the balance between already-created chaperones vs. number of bookings that qualify for them, and adjusted the chaperones. That would address the scenario where the user comes back and makes more reservations, and also if he cancels some reservations (which should result in forfeiting the bonus, if it's the case). Most likely it would be not even a trigger but a reoccurring job.
0
Jon JaquesInformation TechnologistAuthor Commented:
Hi vadimrapp1, you definitely hit upon one thing, that I have had to recently take into account, and that is the difference between a chaperone and a regular traveler; if I count the chaperones along with the regular travelers, I find a creeping error in the number of bookings, and the 1 for 10 rule isn't complete. To deal with this, I added a field, "IsChaperone" so that when doing the count I can exclude the Chaperones and guarantee that, for instance, if there are 30 regular travelers, there will be 3 chaperones; it doesn't matter how many travelers per client there are, only the total number of regular travelers for the given trip.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.