[taffer]

> 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]

> > 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.

[taffer]

> Well, you know about triggers!!!

Yes, and I also know about other nefarious things like monkey patching in some OO languages. Doing such things is antisocial behavior and something that 99% of developers don't expect.

> Those written by the people who wrote that trigger, designed the tables, your whole schema.

As I said, there was no documentation.

> you might create a new contract with that customer later and not want to start from scratch

Or you might get sued for non-compliance with the GDPR.

> because someone who doesn't understand these things might have enough privilege and ignorance to do a real DELETE and break your data.

Or you revoke the privileges and create a straightforward procedure. No magic, no surprises. And you can still grant delete privileges to a role that can delete customer records for GDPR reasons.

> 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.

The difference is that SELECT, INSERT, UPDATE, and DELETE are straightforward features that do exactly what you say unless you use another malicious feature recommended by leading database experts not to use:

avoid all triggers, all of them, they are evil. — Tom Kyte

My advice is to avoid using TRIGGERs — Joe Celko

[cryptonector]

I agree about monkey patching (but not code injection).

> As I said, there was no documentation.

Well, there's your problem :)

> Or you might get sued for non-compliance with the GDPR.

Non-sequitur. That's just an argument that your lawyers need to be involved in matters of data retention.

> malicious feature

That's utter nonsense. If it was "malicious", it would have been removed by now. All the evidence says that they are useful.

What next? No foreign keys? Those are typically implemented as internally-generated triggers. That must make them Doubly-malicious, or something.