Results 1 to 3 of 3

Thread: SQL Triggers

  1. #1
    Join Date
    Feb 2014
    Posts
    1

    Unanswered: SQL Triggers

    What should have been somewhat (?) easy has become a pain...

    I am tracking when POs have been emailed, and when they were acknowledged.

    I have a table set up called Acknowledgements with a POID, emailedby, emaildate,ackby, ackdate.

    When the trigger runs on save or update, there are a few possibilities

    1. The POID is not yet in the Acknowledgements table: f not, add it.

    2. The email flag on the PO has been set to emailed: if so, insert the date and the user into the table for that POID into emailedby and emailed date.

    3. The acknowledgement flag has been set to yes: if so, insert that date and user into the Ack table for that POID into ackyby and ackdate

    Potentially, all three things could happen at once, but usually not. Usually a PO is created, kicked around a while (2-3 days), then emailed, and usually 48 hours later acknowledged. So this trigger will normally fire for a different event each time.

    I was going to post what I had so far, but to be honest, its so far in left field its not worth wasting the space. Can anyone help or at least give me some pointers on how to successfully get a trigger firing reliably?

    Thanks!

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    CREATE TABLE foo (
       id           INT			NOT NULL
       )
    GO
    
    CREATE TRIGGER bar ON foo
    FOR INSERT 
    AS
    BEGIN
       UPDATE foo
          SET id = 1 + id
    END
    GO
    
    INSERT INTO foo (id) VALUES (1)
    INSERT INTO foo (id) VALUES (2)
    
    SELECT * FROM foo
    GO
    
    DROP TABLE foo
    GO
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    >> I am tracking when POs have been emailed, and when they were acknowledged. <<

    Those are status changes. Read:
    https://www.simple-talk.com/sql/t-sq...n-constraints/

    >> I have a table set up called Acknowledgments with a po_id, email_emp_id, email_date, ack_emp_id, ack_date.<<

    I had to make the data elements into ISO-11179. But this table is wrong. The history of a purchase order ought to in a table with the state transitions and that table references this detailed table.

    >> 1. The po_id is not yet in the Acknowledgments table: if not, add it. <<

    The DRI should have made this impossible.

    >> 2. The email flag on the PO has been set to emailed: if so, insert the date and the user into the table for that po_id into email_emp_id and emailed date. <<

    FLAG!!?? NOT in RDBMS! That was assembly language, not SQL. The date tell us that the event has occurred and a state change has begun.

    >> 3. The acknowledgment flag has been set to yes: if so, insert that date and user into the Ack table for that po_id into ack_emp_id and ack_date <<

    << repeat the prior outrage here >>

    >> Usually a PO is created, kicked around a while (2-3 days), then emailed, and usually 48 hours later acknowledged. So this trigger will normally fire for a different event each time. <<

    And it would be in the “PO created” state for those days, becoming the “mailed PO” state, becoming the “unacknowledged” state. There is no need for a (ugh!) trigger with its stinky (ugh!)procedural code. You write an SP that closes the end_timestamp for the “unacknowledged” state and begins the “acknowledged” state, which I assume will be pretty immediate with an email.

    TRIGGERs are like any other non-declarative code and need to be avoided. It is a mark of shame Tell you mother you are playing piano in whorehouse, so she can hold her head up in church.

    Seriously, draw the state transition diagrams and map out the business logic. Are there transitions that need to happen within (n) days? What are the failures and how do you handle them? It will make you think.

Posting Permissions

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