4 ms·

Triggers are implicit, have side effects and are not deterministic. They are confusing and surprising. A procedure call is explicit, a trigger is implicit. You

by taffer 7y ago

Triggers are implicit, have side effects and are not deterministic. They are confusing and surprising. A procedure call is explicit, a trigger is implicit. You don't call a trigger, it just happens as a side effect of something else. People tend to forget implicit things. Suddenly you notice that something is acting strangely or slowly in your application. You can look at your functions and procedures and try to find the problem. But if your application has triggers all over the place, how do you know what is going on? A trigger can change a dozen rows, which in turn can change other rows, so changing a single row can fire thousands or millions of triggers. Also, triggers are not fired in a particular order, the database is free to change the query plan according to what it thinks is best at the moment, so triggers are not deterministic. Triggers can sometimes work and sometimes not. Almost everything that can be done with a trigger can be done with a procedure, but explicitly, deterministically and in most cases without side effects.

cryptonector 7y ago
> Triggers are implicit, have side effects and are not deterministic. > ... > A procedure call is explicit, a trigger is implicit. Method dispatch (whether the language be OOP or otherwise) is also "implicit". Think of triggers as controlled code injection.
- taffer 7y ago
  You still have to call a method if you want it to do something. It doesn't simply happen as a side effect of something else. A method call only ever does one thing at a time, not multiple things in a random order. Read this if you still think triggers are a good idea: https://blogs.oracle.com/oraclemagazine/the-trouble-with-triggers https://blogs.oracle.com/oraclemagazine/the-trouble-with-tri...
  - dragonwriter 7y ago
    > A method call only ever does one thing at a time, not multiple things in a random order. That's...not at all guaranteed if it is possible for the method call to make use of concurrency and parallelism constructs. It's actually not at all uncommon for method calls to do a unknown number of things in an unknown order (and it can even have an unknown number of them still in progress when the method returns!)
    - cryptonector 7y ago
      Exactly.
  - cryptonector 7y ago
    Yeah, and in SQL that's INSERT/UPDATE/DELETE on a table/view. Syntactically, using a stored procedure is really no different, and it can have function calls you don't know about because its body could dispatch just like triggers do. This whole thing about triggers being implied/hidden is not based on anything other than taste.
    - taffer 7y ago
      > and it can have function calls you don't know about because its body could dispatch just like triggers do. Please give an example of a stored procedure that does something else than what the code says it will do. > This whole thing [...] is not based on anything other than taste. Good taste: Insert, update and delete doing exactly what the statement says. Procedures doing exactly what the code says. Bad taste: Insert, update and delete surprising you with magic tricks.
      - cryptonector 7y ago
        > > and it can have function calls you don't know about because its body could dispatch just like triggers do. > Please give an example of a stored procedure that does something else than what the code says it will do. That's not what I wrote. I wrote that the caller of an SP need not know anything about the SP's body any more than the caller of a DML need not know anything about triggers. Of course that is true. And of course it's also true that the caller could know all those details in either case.
        taffer 7y ago
        Let's say customer 1234 wants us to delete his/her customer record, so we issue the following statement: DELETE FROM customers WHERE customer_id = 1234; The statement runs successfully and of course we would expect the customer record to be deleted, because that's what we just said, isn't it? However, the customer is still there somehow. What has happened? Was our statement wrong? It turns out our applications has triggers and by surprise the DELETE changed into UPDATE customers SET contract_status = 'terminated' WHERE emp_id = 1234; just because a few years ago someone thought that the logic of terminating a contract should be wrapped in a now long-forgotten trigger that magically transforms a straightforward DELETE into an UPDATE. If the developer had instead put the logic into a procedure like terminate_contract(customer_id), we wouldn't have had this problem in the first place.
dragonwriter 7y ago
> You don't call a trigger You call an instead of trigger implementing updatability of a view, or the select query defining a view, just as much as you call property setters or getters in OOP languages. With the DB triggers, as in many OOP languages (C#, Python), this is an implementation detail obscured from the calling site, which is good for loose coupling, modularity, etc. Your objections, while IMO still overblown, have relevance to some uses of triggers (they are particularly applicable to AFTER triggers and BEFORE triggers other than those implementing constraints, but least applicable to INSTEAD OF triggers implementing view updatability, which is what we are discussing here.)
- taffer 7y ago
  > much as you call property setters or getters in OOP languages Good design is obvious and orthogonal[1]. If you write setters in an OOP language in such a way that they do surprising things, i.e. not just setting a value, then I would call that bad design. > which is good for loose coupling, modularity, etc. What do you gain by using triggers in this case? All you get is mental overhead, because whenever you use DML you have to keep in mind that there might be a trigger hiding somewhere that does strange things. If you call a procedure instead, you make it clear that you want to do more than just a simple update or insert. > [...] it is possible for the method call to make use of concurrency and parallelism constructs [...] to do a unknown number of things in an unknown order Why would I want this? I want my code simple[2], stupid and obvious, and not convoluted, clever and surprising[3]. [1] https://stackoverflow.com/a/1527430 https://stackoverflow.com/a/1527430 [2] https://www.youtube.com/watch?v=rI8tNMsozo0 https://www.youtube.com/watch?v=rI8tNMsozo0 [3] https://en.wikipedia.org/wiki/Principle_of_least_astonishment https://en.wikipedia.org/wiki/Principle_of_least_astonishmen...
  - cryptonector 7y ago
    Why do you think triggers must be astonishing (but OOP not so)??
    - taffer 7y ago
      see https://news.ycombinator.com/item?id=21455027 https://news.ycombinator.com/item?id=21455027

by taffer 7y ago

cryptonector 7y ago

> Triggers are implicit, have side effects and are not deterministic. > ... > A procedure call is explicit, a trigger is implicit. Method dispatch (whether the language be OOP or otherwise) is also "implicit". Think of triggers as controlled code injection.

taffer 7y ago
You still have to call a method if you want it to do something. It doesn't simply happen as a side effect of something else. A method call only ever does one thing at a time, not multiple things in a random order. Read this if you still think triggers are a good idea: https://blogs.oracle.com/oraclemagazine/the-trouble-with-triggers https://blogs.oracle.com/oraclemagazine/the-trouble-with-tri...
- dragonwriter 7y ago
  > A method call only ever does one thing at a time, not multiple things in a random order. That's...not at all guaranteed if it is possible for the method call to make use of concurrency and parallelism constructs. It's actually not at all uncommon for method calls to do a unknown number of things in an unknown order (and it can even have an unknown number of them still in progress when the method returns!)
  - cryptonector 7y ago
    Exactly.
- cryptonector 7y ago
  Yeah, and in SQL that's INSERT/UPDATE/DELETE on a table/view. Syntactically, using a stored procedure is really no different, and it can have function calls you don't know about because its body could dispatch just like triggers do. This whole thing about triggers being implied/hidden is not based on anything other than taste.
  - taffer 7y ago
    > and it can have function calls you don't know about because its body could dispatch just like triggers do. Please give an example of a stored procedure that does something else than what the code says it will do. > This whole thing [...] is not based on anything other than taste. Good taste: Insert, update and delete doing exactly what the statement says. Procedures doing exactly what the code says. Bad taste: Insert, update and delete surprising you with magic tricks.
    - cryptonector 7y ago
      > > and it can have function calls you don't know about because its body could dispatch just like triggers do. > Please give an example of a stored procedure that does something else than what the code says it will do. That's not what I wrote. I wrote that the caller of an SP need not know anything about the SP's body any more than the caller of a DML need not know anything about triggers. Of course that is true. And of course it's also true that the caller could know all those details in either case.
      - taffer 7y ago
        Let's say customer 1234 wants us to delete his/her customer record, so we issue the following statement: DELETE FROM customers WHERE customer_id = 1234; The statement runs successfully and of course we would expect the customer record to be deleted, because that's what we just said, isn't it? However, the customer is still there somehow. What has happened? Was our statement wrong? It turns out our applications has triggers and by surprise the DELETE changed into UPDATE customers SET contract_status = 'terminated' WHERE emp_id = 1234; just because a few years ago someone thought that the logic of terminating a contract should be wrapped in a now long-forgotten trigger that magically transforms a straightforward DELETE into an UPDATE. If the developer had instead put the logic into a procedure like terminate_contract(customer_id), we wouldn't have had this problem in the first place.

dragonwriter 7y ago

> You don't call a trigger You call an instead of trigger implementing updatability of a view, or the select query defining a view, just as much as you call property setters or getters in OOP languages. With the DB triggers, as in many OOP languages (C#, Python), this is an implementation detail obscured from the calling site, which is good for loose coupling, modularity, etc. Your objections, while IMO still overblown, have relevance to some uses of triggers (they are particularly applicable to AFTER triggers and BEFORE triggers other than those implementing constraints, but least applicable to INSTEAD OF triggers implementing view updatability, which is what we are discussing here.)

taffer 7y ago
> much as you call property setters or getters in OOP languages Good design is obvious and orthogonal[1]. If you write setters in an OOP language in such a way that they do surprising things, i.e. not just setting a value, then I would call that bad design. > which is good for loose coupling, modularity, etc. What do you gain by using triggers in this case? All you get is mental overhead, because whenever you use DML you have to keep in mind that there might be a trigger hiding somewhere that does strange things. If you call a procedure instead, you make it clear that you want to do more than just a simple update or insert. > [...] it is possible for the method call to make use of concurrency and parallelism constructs [...] to do a unknown number of things in an unknown order Why would I want this? I want my code simple[2], stupid and obvious, and not convoluted, clever and surprising[3]. [1] https://stackoverflow.com/a/1527430 https://stackoverflow.com/a/1527430 [2] https://www.youtube.com/watch?v=rI8tNMsozo0 https://www.youtube.com/watch?v=rI8tNMsozo0 [3] https://en.wikipedia.org/wiki/Principle_of_least_astonishment https://en.wikipedia.org/wiki/Principle_of_least_astonishmen...
- cryptonector 7y ago
  Why do you think triggers must be astonishing (but OOP not so)??
  - taffer 7y ago
    see https://news.ycombinator.com/item?id=21455027 https://news.ycombinator.com/item?id=21455027