November 13, 2007 at 10:55 am
nice article
look like you've missed () in tip2
if HOST_NAME='Somemachine' then return
has to be
if HOST_NAME()='Somemachine' then return
November 13, 2007 at 12:01 pm
I frequently run into triggers others have created that don't realize that triggers need to account for set-based approaches, not just row-based approaches. When you see something like "SELECT @id = id FROM INSERTED" I end up slapping my forehead.
Also the bit about disabling triggers is a good one that I use frequently. Great article.
November 13, 2007 at 2:42 pm
Tony Rogerson has found that "DISABLING" triggers (at least in 2000) does not disable the underlying performance effect entirely
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/10/27/1248.aspx
So do NOT trust on Disabling when performance is at stake. Drop them instead.
Just my $0.02
* Noel
November 13, 2007 at 3:38 pm
hi Andy,
found your article useful as we currently use triggers to audit some table changes through a third party product. I would also like to see something on performance with triggers, especially now that it hits the tempdb rather than the transaction log.
Perhaps there's already an article on this?
thanks...
November 13, 2007 at 5:05 pm
I'll look around to see what I can find, might be fun to investigate!
November 16, 2007 at 2:37 pm
For #6, you can also change the execution context of the trigger to execute as the execution context you need.
March 25, 2008 at 3:35 pm
Great article! Lots of good info.
I have a question about Tip #3. I understand that the TRIGGER will only fire once per batch... does an INSERT INTO with a SELECT statement for values constitute a single batch. Or would the TRIGGER fire for each row the SELECT returns? For example:
INSERT INTO myTable
( col1, col2, col3 )
SELECT col7, col8, col9
FROM myOtherTable
WHERE col7 = 1
March 25, 2008 at 5:02 pm
Carlos, your example would only fire the Insert Trigger once regardless of how many rows the Select returns. All inserted rows will be available in the INSERTED table available in the trigger.
--Jeff Moden
March 26, 2008 at 9:00 am
Thanks for the response. That was what I suspected.
Same question, different scenario. What happens when one issues two separate INSERT statements in a single batch? For example:
INSERT INTO myTable
( col1, col2, col3 )
VALUES
( val1, val2, val3 )
INSERT INTO myTable
( col1, col2, col3 )
VALUES
( val4, val5, val6 )
March 26, 2008 at 9:02 am
carlos (3/26/2008)
@JeffThanks for the response. That was what I suspected.
Same question, different scenario. What happens when one issues two separate INSERT statements in a single batch? For example:
INSERT INTO myTable
( col1, col2, col3 )
VALUES
( val1, val2, val3 )
INSERT INTO myTable
( col1, col2, col3 )
VALUES
( val4, val5, val6 )
Trigger fires twice. Again - it would fire once per operation, and the virtual inserted and deleted tables would hold all of the affected rows for the given operation.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 26, 2008 at 12:02 pm
To put it a bit differently than Matt... INSERT triggers fire only once per INSERT statement no matter if one row or thousands of rows are affected. Same is true for UPDATE and DELETE triggers... they fire once for each instance of the SQL Statement no matter how many rows are affected.
--Jeff Moden
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply