Re: Protect record from deletion via trigger

On Jul 1, 1:10 pm, DA Morgan <damor..._at_psoug.org> wrote:
> hpuxrac wrote:
> > Tom Kyte says ( and I believe him ) "Triggers are evil" ... therefore
> > don't use them at all or at least minimize their usage.
>
> If you are going to quote Tom, and he did say it, then quote him in
> context:
>
> Responding to the statement:
> "Protecting data integrity is paramount,"
>
> Tom wrote:
>
> "indeed it is, that is why triggers are evil :)
>
> Most of the time, "business logic"/"data logic" that is implemented in
> triggers is done incorrectly (that is what I mean by people abuse
> triggers)."
>
> http://tkyte.blogspot.com/2006/09/classic-example-of-why-i-despise.html
>
> It is not the triggers that are the issue ... it is poor implementations
> with triggers. There is a time and place for triggers. And when used
> correctly they can be the best solution possible.

So you are saying it is possible to use triggers correctly with the unchangeable underlying poor implementation?

I'm wondering if an after delete trigger that inserts the row from a safe place, if necessary, would be appropriate. Not any screwier than an cron that restarts dead processing, is it? Plus the transactional integrity would protect against other users getting in the middle of no record exists. Right? "Among other things, a database trigger does not check data already loaded into a table" says the Concepts manual. Yeah, but we can do stuff, strange if necessary. The exact time when you need to save the row to the safe place depends on what is in it. You might have to have before trigger save it, after trigger restore it.

I'd be wary of using FGAC to do this single thing, just because it seems to affect so many other things. Tough to test everything with a deadline issue. If you are already using it, then it's not a problem.

Another odd hacker thought: create a child table just for this one row... can't delete because of child row, app doesn't know or care about other table, though it might not handle the error correctly - depends on the app and your users whether that is acceptable. Avoids possible read-consistency performance issues of saving/restoring row. The mulish-data approach :-)

jg

--
@home.com is bogus.
We're from the FDIC and we're here to help you.
http://www.darkreading.com/document.asp?doc_id=157855&WT.svl=news1_1