Page 1 of 2 12 LastLast
Results 1 to 15 of 30
  1. #1
    Join Date
    Jan 2008
    Posts
    186

    Unanswered: TRIGGERS vs using code in an sproc

    I have a table where everytime I insert a new row, I want to insert another row in a separate "log" table.

    Now, the natural way to do this is to use a TRIGGER, but I don't have much experience in using them.

    My question: What is the advantage of using a TRIGGER versus just adding a line at the end of my sproc saying INSERT INTO log_table (blah)

    Thoughts?

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    The trigger will ensure that not only will your procedure add an entry in the log table, but that everyone else's procedures will also add entries in the log table.

  3. #3
    Join Date
    Jan 2008
    Posts
    186
    Quote Originally Posted by MCrowley
    The trigger will ensure that not only will your procedure add an entry in the log table, but that everyone else's procedures will also add entries in the log table.
    Ahhh that one completely slipped by me! Thanks!

  4. #4
    Join Date
    Sep 2005
    Posts
    161
    Writing triggers isn't much different than writing stored procedures. It is very important to test a trigger before deployment. There are a few concepts that must be understood.

    Unless you are using an instead of trigger, the data is already inserted into the table by the time the trigger runs.

    Become familiar with the virtual inserted and deleted tables. An update statement populates both the deleted and inserted tables.

    A Trigger can trigger another trigger.

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Quote Originally Posted by dbguyfh
    ...My question: What is the advantage of using a TRIGGER versus just adding a line at the end of my sproc saying INSERT INTO log_table...
    The advantage is actually negative. A reverse would result in positive. A reference to INSERTED/DELETED inside the trigger involves a lot more that meets the eye. I would take a dozen of cursors over 1 trigger, especially the one that logs the activity.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  6. #6
    Join Date
    Jan 2008
    Posts
    186
    Quote Originally Posted by rdjabarov
    The advantage is actually negative. A reverse would result in positive. A reference to INSERTED/DELETED inside the trigger involves a lot more that meets the eye. I would take a dozen of cursors over 1 trigger, especially the one that logs the activity.
    Hmmm, I'm not quite sure I understand. Can you rephrase that?

    Are you saying that I would take a performance hit by using a trigger to perform an insert or update, rather than just including it in my sproc?

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Exactly. Sometimes I use a lot more words to describe things that can be expressed in 1 or 2. The last sentence is yet another example of it
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Is this observable in execution plans Robert or do you need to run some traces? I have never noticed discernible performance problems approaching that of 12 cursors.

    I second Wage Drone's Integrity Initiative. I wait for Robert to expand on the Performance Paradox.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Jan 2008
    Posts
    186
    Okay, I'm stuck on creating this trigger, and the books online are of no help

    1. My trigger needs a specific value from a field of the row I just inserted. How can I get this??
    Code:
    CREATE TRIGGER ut_myTrigger
    ON myTable
    AFTER INSERT
    AS
    BEGIN
    	DECLARE @someValue INT;
    
    	-- How can i do this??
    	SET @someValue = row_i_just_inserted["myField"]
    END
    GO
    2. What's the difference between FOR and AFTER? They are very vague in their description.

    Thanks!

  10. #10
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    OK, here it is. Every time you reference those 2 tables, the following happens:
    - tlog memory area is locked
    - the section of tlog that contains the transaction is re-read
    - the contents are put to tlog memory
    - if the number of records is too big to fit (I don't know the size of tlog memory), then they get spilled into a worktable in tempdb, and dbcache is used
    - kernel thread(-s) that is involved in this operation is tied up, and new thread will not be created (as opposed to user-mode threads), which means you may start getting kernel thread queueing, and waittype will be IO_COMPLETION, LOGMGR, and WRITELOG.

    In addition, the more activity you have to log, the more chances that you'll start getting blocking and possibly timeouts...Magnitude of consequences may vary, but the question was "what's better", so I answered
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by dbguyfh
    1. My trigger needs a specific value from a field of the row I just inserted. How can I get this??
    Code:
    SET @someValue = row_i_just_inserted["myField"]
    Quote Originally Posted by cascred
    Become familiar with the virtual inserted and deleted tables. An update statement populates both the deleted and inserted tables.
    This message is too short.
    George
    Home | Blog

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Nice answer. Is that documented anywhere for further study?(new to me all that lot)
    Quote Originally Posted by rdjabarov
    Magnitude of consequences may vary, but the question was "what's better", so I answered
    Yah. But if the question is changed to "Which is better to ensure consistent handling of ALL executed statements (procs, ad-hoc, bulk inserts etc)?" trigger wins hands down
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Actually ignore my question. I'll dig some links, post here and let you know if I get stuck.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Cool beans

    Now, in your previous post you are absolutely right. The trigger will win! But your performance will lose Besides, another reason not to use a trigger, - it's an indication that the data architect/dba has no clue where the insert/update/delete is coming from, and THAT I'd take personally. If I am paid as much as I am (not enough though), - I'd better know who modifies my data. And so should anybody who calls themselves a DBA...I am NOT trying to insult anybody here, so don't start this "rdjabarov is this and that, and let's ban him now" thingie
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    rdjabarov is this and that - shall I start a votes to ban him thingie in the mod forum?

    Now - I disagree on your DBA point but we don't want to go on yet another tangent so I'll leave it. Besides, the other stuff you mentioned is much more interesting to me.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •