So, learned something interesting the other day about triggers.
Lets say you have a stored procedure that receives a handful of rows as mine did via XML. These rows could contain entries that need to be inserted into table A, others rows may contain entries that should update already existing entries in table A. So net net there could be 3 situations case where all rows are updates, all rows are new entries and must be inserted, or some rows are updates and some are inserts. My initial approach to this was as follows.
Insert Transactions
(
ID
, Date
, Qty
, Price
, Commission
)
select ID, Date, Quantity, Price, Commission,
from
@SomeTempTable tmp
where
ID not in (select ID from Transactions t where t.Date = tmp.date)
update Transactions
set
Price = tmp.Price
, Qty = tmp.Quantity
, Commission = tmp.Commission
from
@SomeTempTable tmp
, Transactions t
where
t.ID = tmp.ID and t.Date = tmp.date
Pretty simple stuff right? A simple update for existing entries and an insert for new ones. The complication came when the triggers came into play. One for Insert, another for update of the transaction table. I assumed that when there were no rows returned for the select (that feeds the insert) that no entries would be added, and thus there would be nothing for the trigger to do, if it in fact fired at all.
Turns out I was wrong.
My trigger looked something like this.
Declare @ID as int
select @ID = (Select ID from Inserted)
Declare @Date as datetime
select @Date = (select Date from Inserted)
Declare @Qty as float
select @Qty = (select Qty from Inserted)
Declare @Price as money
select @Price = (Select Price from Inserted)
(I then inserted these variables into another table.)
The trigger still fired, and because there was nothing in the INSERTED table to feed the trigger, those values returned as null. And because none of the columns in my table allowed nulls it threw a sql exception. For the longest time I couldn't admit to myself that the trigger was indeed firing w/ nothing to fill the variables, and the problem took a while to figure out.
So the lesson learned here is that if you have a trigger on a db event, it always fired when that keyword is executed, even if the execution of the keyword doesn't actually "do" anything.
