Danny Tramnitzke wrote:
>
> 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
> )
At least using variables in SQL-statements (not IF, WHILE, ...)
has to be done with the parameter-syntax, i.e. :nullcnt
--> select $count into :nullcnt
and so on.
Elke
SAP Labs Berlin
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
