Danny Tramnitzke wrote :
>This trigger-example seems to work, but I didn't get the defined
>Error-Message (errormess).
>When I delete a value in table "Abteilung", which is a foreign key I get
>always the Message
>"KB Stack op illegal".
>Someone told me, that might be a kernel-error or something? What do you
>mean?
>Thats my Trigger, and I want see my own Errormessage, when I delete a row
>in
>Table "Abteilung" with a foreign key (aptnr).
> drop trigger abteilung_delete of Abteilung
//
> Create Trigger abteilung_delete for Abteilung after delete execute
> (
> var errorNo int;
> errormess char(80);
>
> if exists (
> select * from perso
> where perso.aptnr = old.aptnr
> )
>
> then
> errorNo = 800;
> errormess = 'geht nicht, weil Fremdschl�ssel in Perso vorhanden';
> stop (errorNo, errormess);
> )
I think the problem is the exists clause in your trigger. The exists predicate is not
allowed in an
IF-Statement, but unfortunately the compiler does not reject it but produces wrong
code.
You may formulate your trigger as follows :
Create Trigger abteilung_delete for Abteilung after delete execute
(
var errorNo int;
cnt int;
errormess char(80);
select count(*) into :cnt from perso where perso.aptnr = old.aptnr where rowno <=
1;
if cnt > 0
then
begin
errorNo = 800;
errormess = 'geht nicht, weil Fremdschl�ssel in Perso vorhanden';
stop (errorNo, errormess);
end;
)
Caution : this trigger is not sufficient, if you have concurrent inserters into perso
which may
rollback their transaction. If that case is possible, you need a table lock
in share mode
for table perso.
Thomas
--
Thomas Anhaus
SAP DB, SAP Labs Berlin
[EMAIL PROTECTED]
http://www.sapdb.org/
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
