Results 1 to 10 of 10
  1. #1
    Join Date
    Jun 2003
    Location
    Longmont, CO
    Posts
    258

    Unanswered: Triggers - where will they bite me?

    SQL Server 2K

    I'm a DBA-less VB programmer and I'd like to send an email when I stuff data of a certain criteria in my error table. I'd prefer not to have the front end do this as my users don't need to wait for it or participate in the experience.

    In the end this seemed like something that a Trigger could do but I'd like to not start a giant mis-adventure if I can avoid it.

    How scary would it be to have an Insert trigger that, based on a value in the inserted record, would call, say, a DTS package that would email my IT/IS person and tell them that a drive is full?

    Will it behave if I set NOT FOR REPLICATION? We replicate this database frequently.

    Thanks for any insight!

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Re: Triggers - where will they bite me?

    Your a DBAless developer, and you've got replication set up? Who did it?

    Triggers by the way are event driven things.

    The act when an INSERT, DELETE or UPDATE Action occurs against a table.

    They also have the special ability to "see" the before and after images of the data in tables "virtual" table called inserted and deleted. They only live for the length of the transaction.

    You need to know the frequency of the activity, to understand how often it will fire.

    You also have to ability to set up scheduled jobs.

    Also, it sounds like you don't write stored procedures.

    It is a better alternative than putting all the code in VB. Less Network traffic, codes compiled...lots of reasons.

    What do you mean about the "Drive is full part" though?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Aug 2003
    Location
    austin,tx
    Posts
    104

    Re: Triggers - where will they bite me?

    You might get bit doing all that in a trigger since the DTS-email thing could take time to execute. If it fails or there is some error with it (IO, timeout, etc) it could cause the trigger to fail and thus cause the rows to not get inserted/updated into your table.

    I would probably write a stored procedure that does the error-table checking and the emailing. You can then set up a scheduled Job to run however often you like that will run the stored procedure.
    Stored Procedures are no harder to set up than triggers, and jobs are really easy to set up.


    Originally posted by Fly Girl
    SQL Server 2K

    I'm a DBA-less VB programmer and I'd like to send an email when I stuff data of a certain criteria in my error table. I'd prefer not to have the front end do this as my users don't need to wait for it or participate in the experience.

    In the end this seemed like something that a Trigger could do but I'd like to not start a giant mis-adventure if I can avoid it.

    How scary would it be to have an Insert trigger that, based on a value in the inserted record, would call, say, a DTS package that would email my IT/IS person and tell them that a drive is full?

    Will it behave if I set NOT FOR REPLICATION? We replicate this database frequently.

    Thanks for any insight!

  4. #4
    Join Date
    Jun 2003
    Location
    Longmont, CO
    Posts
    258

    What? No stored procedures?

    Hey! I'd like to go on the record as having written more than a few stored procedures--and a few that are miles long.

    However, what I want to do now is:

    1) User attempts feat in front end that passes my app an error message from the every charming and eccentric MS Access saying that a particular drive is full, e.g. Error 61, Disk Full.

    2) My error routine writes a new row in my Errors table on SQL Server.

    Now, most of the errors don't need immediate attention, so I can send those to the IT/IS person at leasure or wait for them to be reviewed. However, if a drive has filled up, I need attention NOW, so...

    3) If an insert in my Errors table indicates that there was a write failure (boolean field = true) then I want to send an email immediately.

    Somehow triggers came to mind and since I've not used triggers before it seemed like a good thing to hack around with... well, if it isn't going to be a disaster, that is.

    Sending email is a snap with the DTS package tools so, of course, I wanted to somewhat limit my challenges and not try to send email from a stored procedure, but I can call a stored procedure from DTS so I think I've got that covered.

    What I really wanted to know was if the trigger might cause problems I don't expect during, say replication.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Re: What? No stored procedures?

    No offense intended...just don't know where you're at...

    I'm glad you've got the sproc thing down...

    And I agree with craig about the overhead thingee...

    But you can't "fire" a note when the error comes in...

    But you're saying the app tells you...why not make the app send the note or page?

    Hey! No offense, ok?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Jun 2003
    Location
    Longmont, CO
    Posts
    258
    Not offended at all, just feeling uppity and thought I'd give you a hard time. I have a thimble's worth of SQL Server skills--and I'm darn proud of 'em!

    RE the FE sending email: There are a whole bunch of reasons why having the front end do the emailing isn't a particularly good thing. Let's just say, its a distributed application and it's in Access. That should be enough.

    So, did I understand this correctly?

    If I set up a trigger that calls a sproc that sends an email the trigger might blow up because things are taking too long?

    And, running an sproc that checks every 2 minutes is a good thing?

  7. #7
    Join Date
    Sep 2003
    Posts
    522
    i would get away from triggers if their activity is going outside of the context of their designated action (insert, update, delete). "firing" a dts package would probably require xp_cmdshell or sp_oaxxx (unless you have something else in mind). it would be more desireable to perform your insert via a stored procedure. this way you could evaluate the error right there and make a decision on what to do with it. also, sending email from dts may seem easier, but in reality it's done the same way you would do it from qa, - xp_sendmail. and i would not recommend to use it due to issues with it. check on cdo or xp_smtp_sendmail.

  8. #8
    Join Date
    Aug 2003
    Location
    austin,tx
    Posts
    104

    Re: What? No stored procedures?

    The only way replication could be affected by the trigger is the same case I laid out earlier for a regular insert. If the DTS fails, the trigger may fail , if the trigger fails, the the insert will fail and will be rolled back, and thus the publication doing the replicating(inserting) would fail.
    This is probably better though, because the insert transaction will not be lost in the case of replication, and it will try it again. Basically you won't have to worry about the email failure causing your error record to be lost.




    Originally posted by Fly Girl
    Hey! I'd like to go on the record as having written more than a few stored procedures--and a few that are miles long.

    However, what I want to do now is:

    1) User attempts feat in front end that passes my app an error message from the every charming and eccentric MS Access saying that a particular drive is full, e.g. Error 61, Disk Full.

    2) My error routine writes a new row in my Errors table on SQL Server.

    Now, most of the errors don't need immediate attention, so I can send those to the IT/IS person at leasure or wait for them to be reviewed. However, if a drive has filled up, I need attention NOW, so...

    3) If an insert in my Errors table indicates that there was a write failure (boolean field = true) then I want to send an email immediately.

    Somehow triggers came to mind and since I've not used triggers before it seemed like a good thing to hack around with... well, if it isn't going to be a disaster, that is.

    Sending email is a snap with the DTS package tools so, of course, I wanted to somewhat limit my challenges and not try to send email from a stored procedure, but I can call a stored procedure from DTS so I think I've got that covered.

    What I really wanted to know was if the trigger might cause problems I don't expect during, say replication.

  9. #9
    Join Date
    Aug 2003
    Location
    austin,tx
    Posts
    104
    ...and yes, it sounds to me like a job that polls the error table every X minutes would be a better choice. You can easily run into problems trying to do lots of processing within the context of a trigger, at least that has been my experience.

  10. #10
    Join Date
    Jun 2003
    Location
    Longmont, CO
    Posts
    258
    OK, thanks for all the info!

    This is just the kind of stuff I needed to know. Thanks for all your help!

Posting Permissions

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