sqlca in stored procedures
I'm starting to use stored procedures in triggers mainly to guaranty
referential integrity on the database, and need to know when an update or
delete has not found any rows, that is, if sqlcode = 100 or sqlerrd[3] > 0.
I have tried to use ON EXCEPTION, but it only catches sqlcodes < 0.
I've seeen here, some time ago, that on version 7+ there exists dbinfo(...)
to get the sqlca.* variables, but as most of my company clients use version 5
I have to continue to support that version.
Right now, the only way I'm seeing to do this is:
select count(*)
into wcount
from table
where ...
if wcount = 0 then
-- do not found code
else
update table
set ...
where ...
end if
This is not very practical programming, apart from degrading performance.
I would also like to know there is any way to use the
record.* notation when using triggers/stored procedures.
For example, right now in 4gl we use auditing in the form:
if audit = true then
insert into audit_table
select * from table
where ...
end if
update table
set ...
where ...
This leads to trouble as auditing is enabled/disabled online on a
table basis, so that when an update/delete is done on a table
we need to first do an select to know if that specific table has auditing
enabled or not.
When auditing is enabled on a table, we would like to create a trigger of
the sort:
create trigger update_table update on table
referencing old as told
for each row ( insert into audit_table (values told.*) )
instead of:
for each row ( insert into audit_table
select * from table where field1 = told.field1, ...)
or
for each row ( insert into audit_table (values told.field1, told.field2,
...)
)
Thanks in advance for any ideas.
--
Carlos Costa e Silva <minimal@mail.telepac.pt>