Hi all
To learn more about the hole SQL Syntax, I guess it's good to have an
example, which contains some tricky PL/SQL Statemens like the following example:
create trigger check_update on example_table for UPDATE as
begin
declare @numrows int,
@nullcnt int,
@validcnt int,
@errno int,
@errmsg varchar(255)
select @numrows = @@rowcount
if
update(function_id)
begin
select @nullcnt = count(*)
from inserted
where function_id is null
select @validcnt = count(*)
from inserted,example_table2
where inserted.function_id = example_table2.function_id
if @validcnt + @nullcnt != @numrows
begin
select @errno = 30007,
@errmsg = 'Cannot UPDATE example_table because example_table2'
if @numrows = 1
begin
select @errmsg = @errmsg + ' "' + convert(varchar(255),function_id)
+ '"'
from inserted
end
end
end
go
I'll be very thankfull if there is anyone, who could translate me that
statement to SAPDB, so that we all have a very usefull template to create more
tricky triggers with using variables and errormessages.
This is my trial-translation, but SQL-Studio returns an errormessage: "Colum
$count not found" :
Drop check_update of example_table
//
CREATE TRIGGER check_update for example_table AFTER UPDATE EXECUTE (
var numrows int;
nullcnt int;
validcnt int;
numrows = $count;
IF NEW.function_id <> OLD.function_id
then
select $count into :nullcnt from example_table where function_id
is null ;
select $count into :validcnt from example_table2
where inserted.function_id =
example_table2.function_id;
if validcnt + nullcnt <> numrows
then
stop (30007, 'Cannot UPDATE example_table because example_table2
exists.') ;
/*if numrows = 1
then */
)
Also I don't know how to return an errormessage like in the if-statement "if
numrows = 1"
Thanks for further help
Danny Tramnitzke
--
+++ 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
