Hi all
Here I have a quite tricky PL/SQL UPDATE trigger, which I have to translate
to SAPDB:
create trigger roti011u on toti011 for UPDATE as
/* UPDATE trigger on toti011 */
begin
declare @numrows int,
@nullcnt int,
@validcnt int,
@insuser_id VARCHAR2(20),
@insfunction_id VARCHAR2(20),
@errno int,
@errmsg varchar(255)
select @numrows = @@rowcount
/* toti012 has toti011 ON CHILD UPDATE RESTRICT */
if
/* %ChildFK(" or",update) */
update(function_id)
begin
select @nullcnt = count(*)
from inserted
where function_id is null
select @validcnt = count(*)
from inserted,toti012
where inserted.function_id = toti012.function_id
if @validcnt + @nullcnt != @numrows
begin
select @errno = 30007,
@errmsg = 'Cannot UPDATE toti011 because toti012'
if @numrows = 1
begin
select @errmsg = @errmsg + ' "' + convert(varchar(255),function_id)
+ '"'
from inserted
end
select @errmsg = @errmsg + ' does not exist.'
goto error
end
end
/* toti010 has toti011 ON CHILD UPDATE RESTRICT */
if
/* %ChildFK(" or",update) */
update(user_id)
begin
select @nullcnt = count(*)
from inserted
where user_id is null
select @validcnt = count(*)
from inserted,toti010
where inserted.user_id = toti010.user_id
if @validcnt + @nullcnt != @numrows
begin
select @errno = 30007,
@errmsg = 'Cannot UPDATE toti011 because toti010'
if @numrows = 1
begin
select @errmsg = @errmsg + ' "' + convert(varchar(255),user_id) +
'"'
from inserted
end
select @errmsg = @errmsg + ' does not exist.'
goto error
end
end
goto ri_ok
error:
raiserror @errno @errmsg
rollback trigger
return
ri_ok:
/* mkddl inserts additional code here */
end
go
my trial to make an SAPDB trigger with same functionality fails, because I
have still not much knowlege in SAPDB Trigger Syntax...
Especially in using variables and handling with Error-messages.
Here is my translation trial:
Drop trigger roti011u of toti011
//
CREATE TRIGGER roti011u for toti011 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 dbadmin.toti011 where function_id
is null ;
select validcnt into $count from dbadmin.toti012
where inserted.function_id =
toti012.function_id;
if validcnt + nullcnt <> numrows
then
stop (30007, 'Cannot UPDATE toti011 because toti012 exists.') ;
IF NEW.user_id <> OLD.user_id
then
select $count into nullcnt from dbadmin.toti010
where user_id is null ;
select $count into validcnt from dbadmin.toti010
where inserted.user_id =
toti010.user_id;
if validcnt + nullcnt <> numrows
then
stop (30007, 'Cannot UPDATE toti011 because toti010');
//if numrows = 1
//then
)
--
+++ 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
