BorlandTalk.com

Matias Surdi · Guest

Hi...
I want to create a trigger rule, or anything to delete all the rows from
table1 when a user delete only one record matching the key of the table..

example:
cod - desc
1 - invoice1
1 - invoice2
1 - invoice3
2 - invoice4

the the user sends : 'delete from table1 where cod=1 and
desc="invoice1"'

and the final status of the table must be
2 - invoice4

I know what I'm doing is not the normal behavior of SQL, i'm
"overriding" it, but I need to ensure that EXISTS ALL OR NOTHING of the
cod key in this table...
I'd tried with rules and triggers, but I cant do a "delete on delete" on
the same table because it call itself recursively...

thanks a lot!!

p/d: I'm using PostgresQL, but I want a general SQL response or a
"design tip" to work this out....

Hans van Leth · Guest

Looks to me there is some master-detail relationship between "cod" and
"desc".
My suggestion is to normalize this and use 2 tables.
In the master table define a boolean field eg. "NotAll".
In the detail table define a delete trigger that sets this field when a
record is deleted.
In the master table define an update trigger that deletes all other detail
records when the field gets set.
This latter trigger will only get called once because for all other details
this field is still set, you would clear it after the delete process.
HtH,

Hans van Leth.

"Matias Surdi" <matiass (AT) interlap (DOT) com.ar> schreef in bericht
news:3f6a0985$1 (AT) newsgroups (DOT) borland.com...