CODEWITHSUNDEEP
t-sqltriggers
NealWalters
NealWalters

Reputation: 18207

Why is INSERTED table of an "After Update" Trigger empty?

Playing with a trigger; the concept sounded easy but stuck on why my insert table is empty, as proven by both debugger and print statements.

Context is this: We want to update a FlightAudit table with a key, but we are temporarily forbidden from changing the Flight table. We came up with the "hack" to stuff the key at the end of the "Comments" column with a delimiter, and parse it back out. We were doing a test to see if we could do what we want in an "After Update" trigger instead of an "Instead of Update" trigger. We would parse the comments back to the original value before a semicolon, then use the data after the semicolon to update the Audit table. 

ALTER TRIGGER [dbo].[tr_Flight2_Upd_Comments] ON [dbo].[Flight2]  
AFTER UPDATE 
AS   
BEGIN  

declare @initial_trancount int = @@TRANCOUNT /* this must be at the very top */;

begin try
    /* For performance reasons */   
    SET NOCOUNT ON;  

    DECLARE @Comments varchar(100); 
    DECLARE @Type char(1);  
    DECLARE @FieldsUpdated XML;      
    DECLARE @ColumnsUpdated VARBINARY(100);  
    DECLARE @AircraftChange bit; 
    DECLARE @NumInsertedRowsForDebug int; 

    SELECT Top 1 @Comments = Inserted.Comments FROM inserted 
    SELECT @NumInsertedRowsForDebug = COUNT(*) from inserted 
-- Here is where see this issue, @Comments is null & 
    print 'Comments=' + IsNull(@Comments,'null') 
    print 'NumRows=' + convert(varchar(10),@NumInsertedRowsForDebug)

    -- Action  
    IF (UPDATE(Comments)) 
           BEGIN 
               Print 'This part works fine' 
               -- logic here removed that sets @NewComments 
               UPDATE Flight 
               SET Comments = @NewComments
               FROM Inserted, Flight2
               WHERE Inserted.FlightId = Flight2.FlightID 
           END 
etc...
END

Test script: 

update flight2 
   set Comments = 'Test22;DL 123420120711SLC;2011-01-01 00:00:00.0000000', 
       ShortCode = 'DL' 
   where FlightId = 'D1448AF1-1F00-41C6-B2F6-2F2EE1BACE07' 
select Comments, ShortCode, * from Flight2

Upvotes: 0

Views: 1122

Answers (1)

NealWalters
NealWalters

Reputation: 18207

Just moments after posting, I saw the issue. We had cloned a real table called "Flight" to a test table called 'Flight2'.

I'm not sure why, but the first UPDATE statement below seemed to be the cause of the INSERTED table being empty, because when I changed it to the second update statment, the INSERTED table then worked as expected. 

UPDATE Flight 
               SET Comments = @NewComments
               FROM Inserted, Flight
               WHERE Inserted.FlightId = Flight.FlightID 

UPDATE Flight 
               SET Comments = @NewComments
               FROM Inserted, Flight2
               WHERE Inserted.FlightId = Flight2.FlightID

Upvotes: 1

Related Questions