Hi all Thanks for fast support!
Thomas, your Triggerexample causes also an error with SQL-Studio 7.4, because there are two where statements in one select statement. SQL-Studio wants have an in front of the second 'where'... (see below) but the example from Pavel Konovalov works fine! Danny > > >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/ -- +++ GMX - Mail, Messaging & more http://www.gmx.net +++ NEU: Mit GMX ins Internet. Rund um die Uhr f�r 1 ct/ Min. surfen! _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general
