before delete trigger question

[duilio foschi]

using FB v. 2.1 32bit on windows 7.

I created a minimal table TEST: 

create table test (

id integer,

deleted smallint default 0)

I insert a record with

insert into test (

id ),

values (

1)

Now I would like to add a BEFORE DELETE trigger that will set field DELETED=1 _instead of deleting_ the record.

I tried 

CREATE TRIGGER test_BEFDEL FOR test

  BEFORE DELETE

AS

begin

   old.deleted=1;

   exception DELETE_FAILED 'no delete for this table';

end

However, when I try to compile the code, I get the following error message:

"attempted update of read-only columns".

Is there no way to get the behaviour I wish?

Thank you

Peppe Polpo

 

[Mathias Pannier (unitel)]

Hi,

 

I think this is not possible. The exception will “revoke” the update and “crash” the whole transaction.

 

You can raise an exception in an on delete trigger or revoke the “delete-grant” to prevent deleting on database level. In your application you can have a delete button for the user, but Your code must do an update not a delete.

 

Regards

M. Pannier

--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/CABkiRz8xyXj%2BwhZ0tWPHmFpPew_sb9cN8k2uUYQFGfWCdHAJvw%40mail.gmail.com.

ub.unitel GmbH, Schulstraße 16, 06792 Sandersdorf-Brehna
Geschaeftsfuehrung Klaus Richter, Olaf Meyer
Amtsgericht Stendal
HRB 26389 FA Bitterfeld Steuernr. 116/107/08597 Ust.identNr. DE815796778
Deutsche Bank IBAN DE53 86070024 0 6143234 00
Kreissparkasse Anhalt-Bitterfeld IBAN DE69 80053722 0 3050326 82
_____________________________________________________________________
Dieses E-Mail ist nur für den Empfänger bestimmt, an den es gerichtet
ist und kann vertrauliches bzw. unter das Berufsgeheimnis fallendes
Material enthalten. Jegliche darin enthaltene Ansicht oder Meinungs-
äußerung ist die des Autors und stellt nicht notwendigerweise die
Ansicht oder Meinung von ub.unitel GmbH dar.
Sind Sie nicht der Empfänger, so haben Sie diese E-Mail irrtümlich
erhalten und jegliche Verwendung, Veröffentlichung, Weiterleitung,
Abschrift oder jeglicher Druck dieser E-Mail ist strengstens untersagt.
_____________________________________________________________________

[Dimitry Sibiryakov]

19.07.2021 11:08, duilio foschi wrote:
> Is there no way to get the behaviour I wish?

Do not delete from the table directly. Either use API of stored procedures or updatable
view.

--
WBR, SD.

[duilio foschi]

the trigger would have spared me a lot of work :(

Too bad...

Thank you

Peppe

--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/ad6a288e-a2f2-1073-2f59-ba5cbf971e0c%40ibphoenix.com.

[Dimitry Sibiryakov]

19.07.2021 11:53, duilio foschi wrote:
> the trigger would have spared me a lot of work :(

A trigger on the view has no difference from a trigger on the table. What is "a lot of
work"?

--
WBR, SD.

[duilio foschi]

maybe I did not understand well your former message and there is still hope :)

"Do not delete from the table directly. Either use API of stored procedures or updatable

view"

Could you please try to expand on it? The message sounds rather cryptic to me...

Thank you

Peppe

--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/8013c98c-d23e-2a3e-7989-e75d780bc024%40ibphoenix.com.

[Dimitry Sibiryakov]

19.07.2021 12:00, duilio foschi wrote:
> Could you please try to expand on it?

1. rename your table.
2. create view with the old name of the table as "select .... from the_table where deleted
<> 1".
3. create an after delete trigger on the view with "update the_table set deleted = 1".
4. profit.

It is actually much less work that adding the condition "deleted <> 1" to every query
in your application.

--
WBR, SD.

[duilio foschi]

genial!

Thank you!

Peppe

--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/00fa0380-8dca-7912-2710-c895c93a339e%40ibphoenix.com.

[Dimitry Sibiryakov]

19.07.2021 12:07, duilio foschi wrote:
> genial!

It is a knowledge of basic database design patterns.

--
WBR, SD.

[Martijn Tonies (Upscene Productions)]

Dimitry,

Won't this become increasingly slower with a lot of records? There's not
much to index on 1/0.


With regards,

Martijn Tonies
Upscene Productions
https://www.upscene.com

Database Workbench - developer tool for Oracle, MS SQL Server, PostgreSQL,
SQL Anywhere, MySQL, InterBase, NexusDB and Firebird.

--
You received this message because you are subscribed to the Google Groups
"firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an
email to firebird-suppo...@googlegroups.com.
To view this discussion on the web, visit

https://groups.google.com/d/msgid/firebird-support/00fa0380-8dca-7912-2710-c895c93a339e%40ibphoenix.com.

[duilio foschi]

>It is a knowledge of basic database design patterns

true. 

AFTER I read your suggestion, I asked myself: "why didnt I think this myself"? :)

Thanks again

Peppe

--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/4f476c07-d2bf-72b1-d0a0-dafbf3152610%40ibphoenix.com.

[Dimitry Sibiryakov]

19.07.2021 12:11, Martijn Tonies (Upscene Productions) wrote:
> Won't this become increasingly slower with a lot of records? There's not much to index on
> 1/0.

There should be no index on this field unless "deleted" records is >50% of whole amount.

If performance is critical and is proven to be unacceptable low, the approach is contrary:

1. Create a separate table for deleted records.
2. In after delete trigger move the deleted records into the table.
3. If for application purpose is needed to see the whole set - create a view with UNION
from two these tables inside.

This is a "partitioning for poor".

--
WBR, SD.

[Martijn Tonies (Upscene Productions)]

Hello,

Yes, the two tables approach was what I was thinking about ;)

With regards,

Martijn Tonies
Upscene Productions
https://www.upscene.com

Database Workbench - developer tool for Oracle, MS SQL Server, PostgreSQL,
SQL Anywhere, MySQL, InterBase, NexusDB and Firebird.

-----Original Message-----
From: Dimitry Sibiryakov
Sent: Monday, July 19, 2021 12:18 PM
To: firebird...@googlegroups.com
Subject: Re: [firebird-support] before delete trigger question

--
You received this message because you are subscribed to the Google Groups
"firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an
email to firebird-suppo...@googlegroups.com.
To view this discussion on the web, visit

https://groups.google.com/d/msgid/firebird-support/dac9781f-ba4b-c23a-0d00-07349f5ac70c%40ibphoenix.com.

[Dimitry Sibiryakov]

19.07.2021 12:20, Martijn Tonies (Upscene Productions) wrote:
> Yes, the two tables approach was what I was thinking about ;)

Such things really should be in a "database design" lectures course of any IT university...

--
WBR, SD.

[duilio foschi]

> Such things really should be in a "database design" lectures course of any IT university...

I attended the "wrong" courses: I am a physicist  :)

Peppe

--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/1053fa88-cd0f-feaa-f98d-798cce1278bd%40ibphoenix.com.

[Martijn Tonies (Upscene Productions)]

And for those who aren't in school, I can highly recommend this book
https://www.amazon.com/dp/0201485559/?tag=dd0ff7-20

With regards,

Martijn Tonies
Upscene Productions
https://www.upscene.com

Database Workbench - developer tool for Oracle, MS SQL Server, PostgreSQL,
SQL Anywhere, MySQL, InterBase, NexusDB and Firebird.

-----Original Message-----
From: Dimitry Sibiryakov
Sent: Monday, July 19, 2021 12:23 PM
To: firebird...@googlegroups.com
Subject: Re: [firebird-support] before delete trigger question

--
You received this message because you are subscribed to the Google Groups
"firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an
email to firebird-suppo...@googlegroups.com.
To view this discussion on the web, visit

https://groups.google.com/d/msgid/firebird-support/1053fa88-cd0f-feaa-f98d-798cce1278bd%40ibphoenix.com.

[Tim Crawford]

Two problems:
1) You cannot both update row values AND throw an exception in a trigger
2) You cannot update row values in a delete trigger, it doesn't make sense

But you can do the following.

Note:
Syntax and functionality if for 2.5.7 +
Also I cannot say 100% this is absolutely safe, but it does work:
 
set term ^ ;
create or ALTER
trigger  test_BEFDEL
active   before DELETE
position 10
on       TEST
as -- Sets deleted to 0 when deletion attempted
   -- and throws exception
BEGIN
  in autonomous TRANSACTION do
    update TEST t set t.DELETED = 0 where t.ID = old.id;
  EXCEPTION DELETE_FAILED 'No delete for table TEST';
END
^
set Term ; ^
commit;

I notice in your code your insert has DELETED = 1 so not sure
how you planned to determine if the trigger worked....
So trigger code below sets it to 0 instead of 1 as per your original try

The row being delete is updated in an independent transaction
with the value 0 (assuming started at 1) prior to throwing the exception
Autonomous transactions always commit as soon as executed,
separately from the current transaction you are in.

Warning: When verifying this, you may have to do a commit
in your query tool and re-query the table to see the update happened.
(due to the autonomous transaction involved)

Tim Crawford

--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/CABkiRz8xyXj%2BwhZ0tWPHmFpPew_sb9cN8k2uUYQFGfWCdHAJvw%40mail.gmail.com.