4 ms·

> 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

by 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.
  - cryptonector 7y ago
    There's nothing wrong with having a trigger that turns deletes into updates, if that's what you want. Why would you be surprised that you can do this, or that it's been done? Your schema is an interface here, so read its docs! EDIT: BTW, it's quite reasonable to have a policy to not delete, but mark deleted/terminated, rows in certain tables. This is especially important for security-relevant tables where the names of entities are liable to get written down in external (to the DB) ACLs. It's quite reasonable to implement such a policy in the DB as triggers -- indeed, there's no better way to do it, since otherwise a user who doesn't read the docs (like you) but does have privilege, might break the database. So, actually, I really think you've completely misunderstood. What you thought was a problem was likely a solution to a bigger problem that you were not aware of. If you removed that trigger, you likely broke something. Meanwhile you're convinced triggers are a bad idea and spread that around as gospel.
    - taffer 7y ago
      > Why would you be surprised[...] The statement says DELETE and that is what I want, the rdms executes it and tells me "deleted 1" (yes, it does). What am I supposed to think? > Your schema is an interface here, so read its docs! What docs? Apparently triggers are obvious and self-documenting, or at least that's what the guy who wrote them thought. > there's no better way to do it, since otherwise a user who doesn't read the docs (like you) but does have privilege, might break the database. Why properly revoke the deletion permission and offer terminate_contract(customer_id) as an alternative if you can use a magic trick instead?
      - cryptonector 7y ago
        > > Why would you be surprised[...] > > The statement says DELETE and that is what I want, the rdms executes it and tells me "deleted 1" (yes, it does). What am I supposed to think? Well, you know about triggers!!! So you're supposed to think that the expected thing happened, whatever the schema designer intended. Whoever wrote that trigger did it for a very good reason, probably the one I gave earlier (namely: to prevent name reuse, and also because gee, you might create a new contract with that customer later and not want to start from scratch). > > Your schema is an interface here, so read its docs! > > What docs? Those written by the people who wrote that trigger, designed the tables, your whole schema. > Why properly revoke the deletion permission and offer terminate_contract(customer_id) as an alternative if you can use a magic trick instead? (I assume you meant to write "Why _not_ ...".) Well, for one, because someone who doesn't understand these things might have enough privilege and ignorance to do a real DELETE and break your data. Moreover, the logical operation here is a DELETE! Just because you mark the row deleted instead does not make it not a logical deletion. You're choosing to take one aspect of SQL RDBMSes extremely literally while then also choosing to ignore or malign another part. That's not a recipe for success.

by taffer 7y ago

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.
- cryptonector 7y ago
  There's nothing wrong with having a trigger that turns deletes into updates, if that's what you want. Why would you be surprised that you can do this, or that it's been done? Your schema is an interface here, so read its docs! EDIT: BTW, it's quite reasonable to have a policy to not delete, but mark deleted/terminated, rows in certain tables. This is especially important for security-relevant tables where the names of entities are liable to get written down in external (to the DB) ACLs. It's quite reasonable to implement such a policy in the DB as triggers -- indeed, there's no better way to do it, since otherwise a user who doesn't read the docs (like you) but does have privilege, might break the database. So, actually, I really think you've completely misunderstood. What you thought was a problem was likely a solution to a bigger problem that you were not aware of. If you removed that trigger, you likely broke something. Meanwhile you're convinced triggers are a bad idea and spread that around as gospel.
  - taffer 7y ago
    > Why would you be surprised[...] The statement says DELETE and that is what I want, the rdms executes it and tells me "deleted 1" (yes, it does). What am I supposed to think? > Your schema is an interface here, so read its docs! What docs? Apparently triggers are obvious and self-documenting, or at least that's what the guy who wrote them thought. > there's no better way to do it, since otherwise a user who doesn't read the docs (like you) but does have privilege, might break the database. Why properly revoke the deletion permission and offer terminate_contract(customer_id) as an alternative if you can use a magic trick instead?
    - cryptonector 7y ago
      > > Why would you be surprised[...] > > The statement says DELETE and that is what I want, the rdms executes it and tells me "deleted 1" (yes, it does). What am I supposed to think? Well, you know about triggers!!! So you're supposed to think that the expected thing happened, whatever the schema designer intended. Whoever wrote that trigger did it for a very good reason, probably the one I gave earlier (namely: to prevent name reuse, and also because gee, you might create a new contract with that customer later and not want to start from scratch). > > Your schema is an interface here, so read its docs! > > What docs? Those written by the people who wrote that trigger, designed the tables, your whole schema. > Why properly revoke the deletion permission and offer terminate_contract(customer_id) as an alternative if you can use a magic trick instead? (I assume you meant to write "Why _not_ ...".) Well, for one, because someone who doesn't understand these things might have enough privilege and ignorance to do a real DELETE and break your data. Moreover, the logical operation here is a DELETE! Just because you mark the row deleted instead does not make it not a logical deletion. You're choosing to take one aspect of SQL RDBMSes extremely literally while then also choosing to ignore or malign another part. That's not a recipe for success.