Thread: SQL Triggers
02-05-14, 15:17 #1Registered User
- Join Date
- Feb 2014
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?
02-05-14, 16:01 #2Resident Curmudgeon
Provided Answers: 54Code:
- Join Date
- Feb 2004
- In front of the computer
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 GOIn theory, theory and practice are identical. In practice, theory and practice are unrelated.
02-09-14, 22:28 #3Registered User
Provided Answers: 1
- Join Date
- Jan 2013
>> I am tracking when POs have been emailed, and when they were acknowledged. <<
Those are status changes. Read:
>> 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.