Hello
Seems to be rightm but an errormessage comes after the statement:
select count(*) into :numrows from example_table
where new.function_id = example_table.function_id;
"Tablename must be from list" after 'where' comes. also, if I set a ':' in
front of NEW ..
> 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
>
--
+++ 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
