Danny Tramnitzke wrote :
>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"
CREATE TRIGGER check_update for example_table AFTER UPDATE EXECUTE (
var numrows int;
nullcnt int;
validcnt int;
errorNo int;
errorMsg char(255);
IF NEW.function_id <> OLD.function_id
then
BEGIN
select count(*) into :numrows from example_table
where new.function_id = example_table.function_id;
errorNo = 0;
select count(*) into :nullcnt from example_table where function_id is null
;
select count(*) into :validcnt from example_table2
where new.function_id = example_table2.function_id;
if validcnt + nullcnt <> numrows
then
begin
errorNo = 30007
errorMsg = 'Cannot UPDATE example_table because example_table2
exists.';
if numrows = 1
then
errorMsg = errorMsg || ' "' || chr(NEW.function_id) || '"';
STOP (errorNo, errorMsg);
end;
end;
)
--
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
