Insert Trigger Firing Before Commit? 1

[xcaliber2222]

Hello,

I'm firing this insert trigger but no data is being returned to the email, I think because it is firing before the commit:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER TRIGGER [dbo].[trTestNotifyShipperIns] ON [dbo].[flight] FOR INSERT AS

 BEGIN

 Declare @flight_id as varchar(32)
	Select @flight_id = flight_id FROM inserted
 Declare @airline_name as varchar(32)
 Declare @flight_num as varchar(32)
 Declare @pu_id as varchar(32)
 Declare @dep_airport_id as varchar(4)
 Declare @arr_airport_id as varchar(4)
 Declare @dep_sch_dttm as varchar(32)
 Declare @dep_act_dttm as varchar(32)
 Declare @arr_sch_dttm as varchar(32)
 Declare @contact_email as varchar(100)
 Declare @customer_name as varchar(100)
 Declare @NewLineChar AS varchar
 SET @NewLineChar = CHAR(13) + CHAR(10)

 SELECT 
	@flight_id = f.flight_id,
	@airline_name=a.name, 
	@flight_num=f.flight_num,
	@pu_id=j.pickup_id,
	@dep_airport_id=f.dep_airport_id, 
    @arr_airport_id=f.arr_airport_id,
	@dep_sch_dttm=f.dep_sch_dttm, 
	@dep_act_dttm=f.dep_act_dttm, 
	@arr_sch_dttm=f.arr_sch_dttm, 
	@customer_name=c.name,
	@contact_email=j.shp_notify_addr
 FROM flight f INNER JOIN
 itinerary_dtl i ON f.flight_id = i.item_id INNER JOIN 
 job j ON i.itinerary_id = j.itinerary_id INNER JOIN
 airline a ON a.airline_id = f.airline_id INNER JOIN
 customer c ON j.cust_code = c.cust_code 
 WHERE i.item_type = 'F' AND j.cust_code = '111111' AND f.flight_id = @flight_id 

 END

 /* BEGIN EMAIL */
 BEGIN  
   Declare @email_message as varchar(2000)
   set @email_message = 'Here is your (updated) flight information for PU#' + @pu_id + '<BR>' + 'Airline: ' + @airline_name + '<BR>' + 'Flight Num: ' + @flight_num + '<BR>' + 'Departing Airport: ' + @dep_airport_id + '<BR>' + 'Arriving Airport: ' + @arr_airport_id + '<BR>' + 'Scheduled Departure Time: ' + @dep_sch_dttm + '<BR>' + 'Actual Departure Time: ' + @dep_act_dttm + '<BR>' + 'Scheduled Arrival Time: ' + @arr_sch_dttm 
   
 INSERT INTO test_email([send_to], [send_from], [subject], [body], [body_format])
   VALUES('softdev@test.com', 'system@test.com', 'Flight Info for ' + @customer_name, @email_message, 'HTML')
 END

I've verified that there are no null values once the record has been inserted. Am I trying to grab data that hasn't been inserted yet? If anyone could help me out I'd really appreciate it.

Thanks,
Alejandro

 

[bborissov]

Instead of flight table use INSERTED one. Also NEVER program triggers with assumption that only one record at a time will be inserted. Remember triggers are fired after the WHOLE job is done, not after every record.

SELECT
    @flight_id = f.flight_id,
    @airline_name=a.name,
    @flight_num=f.flight_num,
    @pu_id=j.pickup_id,
    @dep_airport_id=f.dep_airport_id,
    @arr_airport_id=f.arr_airport_id,
    @dep_sch_dttm=f.dep_sch_dttm,
    @dep_act_dttm=f.dep_act_dttm,
    @arr_sch_dttm=f.arr_sch_dttm,
    @customer_name=c.name,
    @contact_email=j.shp_notify_addr
FROM [COLOR=red][b]INSERTED[/b][/color] f 
INNER JOIN itinerary_dtl i ON f.flight_id = i.item_id
INNER JOIN job j           ON i.itinerary_id = j.itinerary_id
INNER JOIN airline a ON a.airline_id = f.airline_id
INNER JOIN customer c ON j.cust_code = c.cust_code
WHERE i.item_type = 'F'      AND 
      j.cust_code = '111111'

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[xcaliber2222]

Thank you Boris. Of course, I should have seen that with the INSERTED table. Thank you very much.

 

[xcaliber2222]

Borislav ,

There is one more thing. I can run the SQL for the trigger in QA and get results fine, but when the actual trigger fires nothing happens, no email. After more testing I discovered that when a value is updated in our application two new records are inserted into this same table, one for one leg of a flight and one for the other. In other words, even if the update is a small change on one value (say an actual departure date for instance) there will be two new records inserted, one which shows the change and the other which is still the same info but for the other leg of the same flight. Ezch inserted record is given a new flight_id. If two records are being inserted at the same time would this cause a problem with the trigger firing?

Hope this makes sense.

Thanks,
Alejandro