[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Need Help Creating ON INSERT trigger in SQL 2008

Posted on 2014-08-08
11
Medium Priority
?
493 Views
Last Modified: 2014-08-14
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!
0
Comment
Question by:CoastalData
  • 7
  • 2
  • 2
11 Comments
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 40249467
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
 
LVL 3

Author Comment

by:CoastalData
ID: 40249510
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
 
LVL 3

Author Comment

by:CoastalData
ID: 40249544
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
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
LVL 3

Author Comment

by:CoastalData
ID: 40250809
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
 
LVL 3

Author Comment

by:CoastalData
ID: 40254451
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
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 40259251
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
 
LVL 3

Author Comment

by:CoastalData
ID: 40260821
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
 
LVL 3

Author Closing Comment

by:CoastalData
ID: 40261012
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40261023
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
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 40261035
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
 
LVL 3

Author Comment

by:CoastalData
ID: 40261196
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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question