Trigger Trivia

Question

Post reply

Trigger Trivia

Viewing 11 posts - 16 through 25 (of 25 total)

You must be logged in to reply to this topic. Login to reply

Max Yasnytsky SSCrazy Points: 2462 More actions · Answer 1

nice article

look like you've missed () in tip2

if HOST_NAME='Somemachine' then return

has to be

if HOST_NAME()='Somemachine' then return

Aaron Ingold SSCrazy Eights Points: 9070 More actions · Answer 2

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.

noeld SSC Guru Points: 96590 More actions · Answer 3

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

kathrinecallegari Grasshopper Points: 13 More actions · Answer 4

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...

Andy Warren SSC Guru Points: 119902 More actions · Answer 5

I'll look around to see what I can find, might be fun to investigate!

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter

chapman.tim SSC-Addicted Points: 417 More actions · Answer 6

For #6, you can also change the execution context of the trigger to execute as the execution context you need.

los Right there with Babe Points: 782 More actions · Answer 7

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

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 8

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

los Right there with Babe Points: 782 More actions · Answer 9

@jeff

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 )

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 10

carlos (3/26/2008)
@Jeff
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 )

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?

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 11

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)