March 31, 2011 at 12:19 pm
Instead of every insert into a table, can we assign a trigger to fire only for specific condition.
example: trigger should fire only when AccountFK is not NULL in an insert int tblProject ; if AccountFK is populated, don't bother.
March 31, 2011 at 12:42 pm
You can't suppress the trigger entirely based on a condition like that... it's going to fire every time. But you could do that check as the first action of the trigger and have it skip additional processing based on the outcome.
March 31, 2011 at 3:01 pm
repent_kog_is_near (3/31/2011)
example: trigger should fire only when AccountFK is not NULL in an insert int tblProject ; if AccountFK is populated, don't bother.
So it should only fire when it's not null but then don't bother? 😉
Seriously, when do you want it to fire and what do you want the fire action to be?
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
April 1, 2011 at 7:49 am
Seth, there are very few times that column will be populated and hence the trigger will be needed.. and hence for performance sake, i was seeking better option...
April 1, 2011 at 7:51 am
>>when do you want it to fire and what do you want the fire action to be?
the trigger, ideally, should be fired only when there is a new row inserted but wiith a column populated. that column is nullable and most times it may not be populated. If it is populated then the fire action is for some calculation and further updates related to that.
April 1, 2011 at 8:59 am
repent_kog_is_near (4/1/2011)
Seth, there are very few times that column will be populated and hence the trigger will be needed.. and hence for performance sake, i was seeking better option...
You didn't specify what other options were on the table, just asked if you could suppress the trigger, so that's all I answered. There are definitely other options... like using a stored procedure to do the inserts and having it do the additional work in those rare situations.
April 1, 2011 at 9:20 am
repent_kog_is_near (4/1/2011)
>>when do you want it to fire and what do you want the fire action to be?the trigger, ideally, should be fired only when there is a new row inserted but wiith a column populated. that column is nullable and most times it may not be populated. If it is populated then the fire action is for some calculation and further updates related to that.
You could just return from the trigger when there are no AccountFK IDs, like so
create trigger trg_tblProject on tblProject
after insert as
if (select count(*) from inserted where AccountFK is not null) = 0
return;
--now do the work with inserted.AccountFK...
go
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply